NEED HELP WITH FORMULA

%3CLINGO-SUB%20id%3D%22lingo-sub-1596580%22%20slang%3D%22en-US%22%3ENEED%20HELP%20WITH%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22im%22%3EThis%20is%20for%20myself%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3DIF(D2%26lt%3B%3D17.99%2CE2%2B2.5%20%2C%20IF(D2%26lt%3B%3D19.99%20%26gt%3B%3D17.99%2CE2%2B3.5%20%2C%20IF(D2%26lt%3B%3D22.99%20%26gt%3B%3D19.99%2CE2%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%2B4.5%20%2C%20IF(D2%26gt%3B%3D22.99%20%26lt%3B%3D27.99%2CE2%2B5.5%20%2C%20IF(D2%26gt%3B%3D28%2CE2%2B4)))))%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%3ESo%20what%20I%20would%20like%20it%20to%20say%20is%2C%20%3CEM%3E%3CSTRONG%3Eif%20D2%20is%20less%20than%2017.99%20add%202.5%20to%20E2%2C%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FSPAN%3E%3CEM%3E%3CSTRONG%3E%3CSPAN%20class%3D%22im%22%3E%3CBR%20%2F%3Ethough%20if%20D2%20is%20smaller%20than%20or%20equal%20to%2019.99%20but%20bigger%20than%20or%20equal%20to%3CBR%20%2F%3E17.99%20add%203.5%20to%20E2%2C%20though%20if%20D2%20is%20smaller%20than%20or%20equal%20to%2027.99%20but%3CBR%20%2F%3Ebigger%20than%2022.99%20then%20add%205.5%20to%20E2%2C%20though%20if%20D2%20is%20bigger%20than%2028.00%20add%3CBR%20%2F%3E%3C%2FSPAN%3E4%20to%20E2%3C%2FSTRONG%3E%3C%2FEM%3E%3CSPAN%20class%3D%22im%22%3E%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3EDoes%20this%20sentence%20reflect%20the%20formula%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1596580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596697%22%20slang%3D%22en-US%22%3ERe%3A%20NEED%20HELP%20WITH%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F763255%22%20target%3D%22_blank%22%3E%40jayzed%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20but%20your%20syntax%20is%20incorrect.%3C%2FP%3E%3CP%3EIn%20Excel%2C%20this%20expression%3A%20D2%26lt%3B%3D19.99%20%26gt%3B%3D17.99%3CBR%20%2F%3Ewould%20be%3A%20AND(D2%26lt%3B%3D19.99%2C%20D2%26gt%3B%3D17.99)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20it's%20not%20really%20necessary%20because%20if%20your%20first%20test%20(D2%26lt%3B%3D17.99%20)%20fails%2C%20then%20D2%20must%20be%20%26gt%3B%2017.99%20so%20there%20is%20no%20need%20to%20test%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20would%20suggest%20rounding%20both%20sides%20of%20your%20conditions%20to%202%20decimals%20to%20avoid%20any%20problems%20with%20double%20precision%20point%20math%2C%20which%20can%20cause%20rounding%20errors%20at%20the%20hardware%20level%20(doesn't%20matter%20that%20your%20data%20is%20only%20to%20two%20decimals).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DE2%2BIF(ROUND(D2%2C2)%26lt%3B%3DROUND(17.99%2C2)%2C2.5%2CIF(ROUND(D2%2C2)%26lt%3B%3DROUND(19.99%2C2)%2C3.5%2CIF(ROUND(D2%2C2)%26lt%3B%3DROUND(22.99%2C2)%2C4.5%2CIF(ROUND(D2%2C2)%26lt%3B%3DROUND(27.99%2C2)%2C5.5%2C4))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20you%20could%20create%20a%20lookup%20table%20(say%20the%20table%20is%20in%20A1%3AB6)%3A%3CBR%20%2F%3E0%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202.50%3CBR%20%2F%3E18%26nbsp%3B%20%26nbsp%3B%203.50%3CBR%20%2F%3E20%26nbsp%3B%20%26nbsp%3B%204.50%3CBR%20%2F%3E23%26nbsp%3B%20%26nbsp%3B%205.50%3CBR%20%2F%3E28%26nbsp%3B%20%26nbsp%3B%204.00%3CBR%20%2F%3E%E2%88%9E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20your%20forumula%20is%3A%3CBR%20%2F%3E%3DE2%2BVLOOKUP(ROUND(D2%2C2)%2C%24A%241%3A%24B%246%2C2%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596754%22%20slang%3D%22en-US%22%3ERe%3A%20NEED%20HELP%20WITH%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596754%22%20slang%3D%22en-US%22%3EThank%20u%20so%20much%20%3Asmiling_face_with_smiling_eyes%3A%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This is for myself
=IF(D2<=17.99,E2+2.5 , IF(D2<=19.99 >=17.99,E2+3.5 , IF(D2<=22.99 >=19.99,E2
+4.5 , IF(D2>=22.99 <=27.99,E2+5.5 , IF(D2>=28,E2+4)))))

So what I would like it to say is, if D2 is less than 17.99 add 2.5 to E2,
though if D2 is smaller than or equal to 19.99 but bigger than or equal to
17.99 add 3.5 to E2, though if D2 is smaller than or equal to 27.99 but
bigger than 22.99 then add 5.5 to E2, though if D2 is bigger than 28.00 add
4 to E2


Does this sentence reflect the formula?

2 Replies
Highlighted

@jayzed 

 

Yes, but your syntax is incorrect.

In Excel, this expression: D2<=19.99 >=17.99
would be: AND(D2<=19.99, D2>=17.99)

 

But, it's not really necessary because if your first test (D2<=17.99 ) fails, then D2 must be > 17.99 so there is no need to test that.

 

Also, I would suggest rounding both sides of your conditions to 2 decimals to avoid any problems with double precision point math, which can cause rounding errors at the hardware level (doesn't matter that your data is only to two decimals).

 

=E2+IF(ROUND(D2,2)<=ROUND(17.99,2),2.5,IF(ROUND(D2,2)<=ROUND(19.99,2),3.5,IF(ROUND(D2,2)<=ROUND(22.99,2),4.5,IF(ROUND(D2,2)<=ROUND(27.99,2),5.5,4))))

 

Alternatively, you could create a lookup table (say the table is in A1:B6):
0      2.50
18    3.50
20    4.50
23    5.50
28    4.00

 

Then, your forumula is:
=E2+VLOOKUP(ROUND(D2,2),$A$1:$B$6,2,1)

Highlighted
Thank u so much