SOLVED

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2108476%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108476%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%20I'm%20sure%20some%20of%20you%20will%20find%20this%20a%20simple%20solution%2C%20but%20I%20am%20new%20to%20Formulas%20and%20I%20just%20cant%20figure%20out%20a%20formula%20for%20what%20my%20boss%20wants%20me%20to%20create%2C%20PLEASE%20HELP...he%20wants%20me%20to%20create%20a%20spreadsheet%20for%20when%20we%20install%20systems%20into%20hotels%2C%20I%20have%20the%20sheet%20almost%20done%2C%20but%20on%20one%20line%2C%20he%20only%20wants%20to%20charge%20this%20line%20item%20to%20the%20customer%20if%20the%20room%20count%20is%20at%20130%20or%20more%2C%20and%20if%20it%20is%20130%20or%20more%2C%20he%20wants%20the%20room%20count%20to%20automatically%20fill%20in%20on%20Line%20item%20B11.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20when%20we%20fill%20in%20the%20room%20count%20on%20line%20B5%20which%20is%20where%20I%20will%20always%20fill%20in%20the%20room%20count%20for%20each%20property%2C%20my%20sheet%20auto%20fills%20in%20the%20room%20count%20on%20B7%20thru%20B10%2C%20and%20I%20have%20that%20all%20set%20and%20done%2C%20%3CSTRONG%3EBUT%3C%2FSTRONG%3E%20on%20%3CSTRONG%3EB11%3C%2FSTRONG%3E%20I%20only%20want%20the%20room%20count%20to%20auto%20fill%20in%20for%20that%20said%20room%20count%20if%20its%20130%20or%20more%2C%20if%20its%20less%20that%20130%20we%20don't%20want%20the%20room%20count%20to%20fill%20in%20with%20a%20number%20that%20we%20input%20in%20B5.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2108476%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111808%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947994%22%20target%3D%22_blank%22%3E%40Queenie65%3C%2FA%3E%26nbsp%3BI%20don't%20understand%20your%20comment%2Fquestion%20about%20it%20%22adding%22%20and%20costing%20more.%26nbsp%3B%20The%20formula%20you%20have%2Fhad%20is%20%22SUM(C11*B11)%22%20which%20doesn't%20need%20the%20%22SUM%22%20as%20it%20is%20just%20C11*B11%2C%20but%20the%20answer%20is%20still%20correct.%26nbsp%3B%20As%20shown%20in%20the%20screen%20capture%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mtarler_0-1612302631873.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F251546iE5A5E21CDD128B40%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mtarler_0-1612302631873.png%22%20alt%3D%22mtarler_0-1612302631873.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E180%20units%20time%20the%20(negative)%200.25%20results%20in%20a%20-%2445.00%20and%20the%20per%20month%20is%20%2445%20less%20than%20if%20that%20line%20wasn't%20there.%26nbsp%3B%20Also%20the%20next%20line%20is%20a%20per%20month%2C%20per%20room%20which%20shows%20the%20%240.25%20discount%20%2418.94%20-%20%240.25%20%3D%20%2418.69%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111770%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111770%22%20slang%3D%22en-US%22%3E%3DIF(B11%26gt%3B%3D130%2CSUM(D7%3AD10)*-0.25%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111693%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947994%22%20target%3D%22_blank%22%3E%40Queenie65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20catch%20how%20do%20you%20calculate%20the%20discount%20for%20above%20130%2C%20but%20the%20idea%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DC11*(B11%26gt%3B%3D130)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111630%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BYAY%2C%20that%20formula%20worked%2C%20but%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ED11%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ewon't%20work%3F%26nbsp%3B%20I%20know%20I'm%20pushing%20it%20asking%20another%20question%20lol%2C%20but%20I'm%20going%20to%20ask%20anyways%3A)%3C%2FP%3E%3CP%3ED11%20is%20adding%20the%20unit%20count%20and%20the%20(.25)%20discount%20and%20its%20turning%20into%20a%20positive%20number%20instead%20of%20a%20negative%20number%20for%20a%20discount%2C%20I%20want%20to%20give%20my%20customers%20a%20discount%20if%20their%20room%20count%20is%20130%20or%20more%20rooms.%3C%2FP%3E%3CP%3ESo%20when%20B11%20auto%20fills%20in%20with%20130%20or%20more%20from%20B5%2C%20I%20want%20D11%20to%20be%20a%20negative%20number%20for%20a%20discount%2C%20but%20if%20the%20number%20is%20under%20130%20there%20should%20be%20no%20number%20and%20right%20now%20if%20its%20under%20130%20it%20shows%20(.25)%20instead%20of%20zero%2C%20and%20if%20it's%20130%20or%20more%2C%20its%20charging%20the%20customer%20more%20money%20instead%20of%20giving%20a%20discount.%26nbsp%3B%20This%20will%20be%20the%20last%20question%2C%20I%20appreciate%20this%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111610%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947994%22%20target%3D%22_blank%22%3E%40Queenie65%3C%2FA%3E%26nbsp%3Ba%20formula%20like%20this%20should%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DB5*(B5%26gt%3B%3D130)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111598%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20have%20my%20sheet%20attached%2C%20I%20thought%20I%20had%20it%20figured%20out%2C%20but%20noooooo.%20I%20appreciate%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2108493%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2108493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F947994%22%20target%3D%22_blank%22%3E%40Queenie65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20provide%20small%20sample%20file%20to%20illustrate%20the%20task%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello All,  I'm sure some of you will find this a simple solution, but I am new to Formulas and I just cant figure out a formula for what my boss wants me to create, PLEASE HELP...he wants me to create a spreadsheet for when we install systems into hotels, I have the sheet almost done, but on one line, he only wants to charge this line item to the customer if the room count is at 130 or more, and if it is 130 or more, he wants the room count to automatically fill in on Line item B11. 

So when we fill in the room count on line B5 which is where I will always fill in the room count for each property, my sheet auto fills in the room count on B7 thru B10, and I have that all set and done, BUT on B11 I only want the room count to auto fill in for that said room count if its 130 or more, if its less that 130 we don't want the room count to fill in with a number that we input in B5.

7 Replies

@Queenie65 

Perhaps you may provide small sample file to illustrate the task?

@Sergei Baklan I have my sheet attached, I thought I had it figured out, but noooooo. I appreciate your help!

Best Response confirmed by Queenie65 (New Contributor)
Solution

@Queenie65 a formula like this should work:

=B5*(B5>=130)

 

@mtarler YAY, that formula worked, but D11 won't work?  I know I'm pushing it asking another question lol, but I'm going to ask anyways

D11 is adding the unit count and the (.25) discount and its turning into a positive number instead of a negative number for a discount, I want to give my customers a discount if their room count is 130 or more rooms.

So when B11 auto fills in with 130 or more from B5, I want D11 to be a negative number for a discount, but if the number is under 130 there should be no number and right now if its under 130 it shows (.25) instead of zero, and if it's 130 or more, its charging the customer more money instead of giving a discount.  This will be the last question, I appreciate this so much!!

@Queenie65 

I didn't catch how do you calculate the discount for above 130, but the idea is

=C11*(B11>=130)

 

=IF(B11>=130,SUM(D7:D10)*-0.25,0)

@Queenie65 I don't understand your comment/question about it "adding" and costing more.  The formula you have/had is "SUM(C11*B11)" which doesn't need the "SUM" as it is just C11*B11, but the answer is still correct.  As shown in the screen capture:

mtarler_0-1612302631873.png

180 units time the (negative) 0.25 results in a -$45.00 and the per month is $45 less than if that line wasn't there.  Also the next line is a per month, per room which shows the $0.25 discount $18.94 - $0.25 = $18.69