IF formula not working - Trying to calculate correct shipping costs on items (Excel)

Question

I'm having problems trying to do this formula and it just doesn't work. Can anyone help me?

=IF(JH2="13",CEILING(JD2/0.68+13,0.5)-0.01,""),IF(AND(JH2="6.95",(JD2/0.68))<50,CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01),IF(AND(M2="CA",ISNUMBER(SEARCH(S2,"INCONTINENCE"))),CEILING(JD2/0.68+6.95,0.5)-0.01,""))

Just a FYI it reads,

IF Freight price is 13 THEN to Divide the Cost price by .68 and ADD the 13.

IF Freight price is 6.95 AND the Cost Price Divide .68 is LESS than $50 THEN add $3.00 ELSE ADD 6.95.

IF M2 (which is Unit of Measurement) has CA AND Column S2 (which is the category) contains the word "Incontinence" THEN calculate Cost Price Divide .68 and add 6.95 Regardless.

Everything is rounded up.

But can't get the Damn thing to work.

That's impossible to read. Reformat your question. Also, you need to create a MCVE. — Amit, Feb 06 '16 at 19:38
Changing the question after getting an answer is not proper. A new question gets a new post. — Scott Craner, Feb 06 '16 at 20:24

Scott Craner · Answer 1 · 2016-02-06T19:54:42.657

0

Try this, it is untested:

=IFERROR(CEILING(IF(JH2=13,JD2/0.68+13,IF(AND(JH2=6.95,JD2/0.68<50),JD2/0.68+3,IF(JH2=6.95,JD2/0.68+6.95,IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",S2))),JD2/0.68+6.95,"")))),0.5)-0.01,"")

edited Feb 06 '16 at 19:54

answered Feb 06 '16 at 19:49

Scott Craner

148,073
10
49
81

Thanks Scott, I've just updated the question. Didn't realize at first but just thought it won't work for Child Items. – Mayur Feb 06 '16 at 20:22

score 0 · Accepted Answer · answered Feb 06 '16 at 20:48

First some nagging and whining.

Unreadable source code.
Reference to cells without provided values.

Now the answer (at least a part of it, depending on me missing something else).

If you start breaking up the formula, you'll notice that the first AND has the condition of equality first (that's correct) but then you'll see that the second condition only is a division, whereas the inequality comparison with 50 is put outside.

I believe that's your error. But that's based on the assumption that I got the formula correctly.

As a general suggestion for working with complex formulas in Excel, I usually do a single step at a time (putting the sub-results in separate columns to verify that they're correct). When done and confirmed, I can merge them into a single one.

IF formula not working - Trying to calculate correct shipping costs on items (Excel)

2 Answers2