Home Ask Login Register

Developers Planet

Your answer is one click away!

Mayur February 2016

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

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.

Answers


Scott Craner February 2016

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,"")


Konrad Viltersten February 2016

First some nagging and whining.

  1. Unreadable source code.
  2. 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.

Post Status

Asked in February 2016
Viewed 1,702 times
Voted 5
Answered 2 times

Search




Leave an answer


Quote of the day: live life