Home

Help with Excel functions

%3CLINGO-SUB%20id%3D%22lingo-sub-180402%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180402%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3C%2FP%3E%3CP%3EI'm%20in%20desperate%20need%20of%20help%20with%20a%20problem%20for%20my%20accounting%20class.%20I've%20attached%20the%20file.%20The%20problem%20is%3A%20In%20cell%20H6%2C%20enter%20a%20formula%20to%20determine%20any%20discount%20that%20should%20be%20applied.%20If%20the%20payment%20method%20was%20Express%20Miles%20or%20Rewards%2C%20the%20customer%20should%20receive%20the%20discount%20shown%20in%20B42.%20If%20no%20discount%20should%20be%20applied%2C%20the%20formula%20should%20return%20a%20zero.%20Use%20the%20named%20range%20for%20cell%20B42%2C%20not%20the%20cell%20address%2C%20in%20this%20formula.%20Copy%20the%20function%20down%20the%20column%20to%20cell%20H32.%20I've%20tried%20all%20types%20of%20variations%20of%20the%20%3DIF%20function%20and%20I%20got%20nowhere.%26nbsp%3B%20Thanks%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-180402%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EShow%20and%20Tell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-341176%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-341176%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Yasmin%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20H6%2C%20you%20may%20use%20any%20of%20these%20formulas%3A%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20IF%20function%3A%26nbsp%3B%3DIF(OR(F6%3D%7B%22Express%20Miles%22%2C%22Rewards%22%7D)%2C%3CBR%20%2F%3EE6*G6*Discount%2C%3CBR%20%2F%3E0)%3C%2FP%3E%3CP%3E2.%20Boolean%20formula%3A%26nbsp%3B%3DOR(F6%3D%7B%22Express%20Miles%22%2C%22Rewards%22%7D)*%3CBR%20%2F%3EE6*G6*Discount%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20then%20copy%20your%20chosen%20formula%20down%20to%20H32.%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwifoo%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-339428%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339428%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20need%20to%20give%20a%20name%20to%20cell%20B42%20if%20it%20does%20not%20already%20have%20one.%26nbsp%3B%20highlight%20that%20cell%20and%20check%20in%20the%20upper%20left%20box%20that%20shows%20cell%20addresses.%26nbsp%3B%20If%20it%20doesn't%20say%20B42%2C%20then%20it%20has%20a%20name.%3C%2FP%3E%3CP%3EThe%20formula%20could%20be%20a%20nested%20if%20statement.%26nbsp%3B%20Have%20the%20first%20one%20check%20the%20contents%20of%20the%20payment%20method%20to%20see%20if%20it%20says%20%22Rewards%22%2C%20and%20the%20if%20true%20part%20of%20the%20formula%20would%20show%20the%20amount%20charged%20times%20the%20B42%20cell%2C%20but%20instead%20of%20writing%20B42%2C%20you%20would%20click%20on%20that%20cell%20which%20should%20automatically%20enter%20the%20name.%26nbsp%3B%20You%20could%20also%20just%20enter%20the%20name.%26nbsp%3B%20Then%20the%20if%20false%20portion%20of%20the%20formula%20would%20be%20the%20nested%20IF%20formula%20which%20does%20the%20same%20thing%20except%20it%20check%20for%20%22Express%20Miles%22%2C%20with%20results%20if%20true%20would%20be%20the%20charged%20amount%20times%20that%20same%20range%20name%20for%20the%20discount.%26nbsp%3B%20The%20if%20false%20portion%20of%20the%20formula%20would%20be%20zero%20%220%22).%26nbsp%3B%20You%20should%20be%20able%20to%20just%20copy%20this%20down%20to%20the%20rest%20of%20the%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180409%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180409%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response.%20I%20hope%20I%20have%20some%20luck.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180407%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180407%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(OR(F6%3D%22Express%20Miles%22%2CF6%3D%22Rewards%22)%2CVLOOKUP(A6%2CRentalRates%2C2%2CFALSE)%2C0)%2C0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
yasmin walker
New Contributor

Hello Community,

I'm in desperate need of help with a problem for my accounting class. I've attached the file. The problem is: In cell H6, enter a formula to determine any discount that should be applied. If the payment method was Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount should be applied, the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula. Copy the function down the column to cell H32. I've tried all types of variations of the =IF function and I got nowhere.  Thanks for the help.

4 Replies

That could be like

=IFERROR(IF(OR(F6="Express Miles",F6="Rewards"),VLOOKUP(A6,RentalRates,2,FALSE),0),0)

Thank you for your response. I hope I have some luck.

You need to give a name to cell B42 if it does not already have one.  highlight that cell and check in the upper left box that shows cell addresses.  If it doesn't say B42, then it has a name.

The formula could be a nested if statement.  Have the first one check the contents of the payment method to see if it says "Rewards", and the if true part of the formula would show the amount charged times the B42 cell, but instead of writing B42, you would click on that cell which should automatically enter the name.  You could also just enter the name.  Then the if false portion of the formula would be the nested IF formula which does the same thing except it check for "Express Miles", with results if true would be the charged amount times that same range name for the discount.  The if false portion of the formula would be zero "0").  You should be able to just copy this down to the rest of the cells.

Hello Yasmin,

 

For H6, you may use any of these formulas: 

1. IF function: =IF(OR(F6={"Express Miles","Rewards"}),
E6*G6*Discount,
0)

2. Boolean formula: =OR(F6={"Express Miles","Rewards"})*
E6*G6*Discount

 

You can then copy your chosen formula down to H32. 

Cheers!

 

Twifoo

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies