Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel formula - Pricing based on quantity

Sep 16, 2010 1:36AM PDT

I am creating a purchasing form. The items have a sliding scale for pricing (the more you buy, the cheaper they are). So the Quantity price (N10) in the formula effects the total price. I have three different pricing amounts and can only get two to work. How do I add in the third IF statement to make it work? See formula below:

=IF(ISBLANK(#REF!),IF(ISNUMBER(I10),IF(N10>=I10,N10*L10,N10*K10),N10*K10),IF(ISNUMBER(I10),IF(N10>=I10,N10*L10,N10*K10),N10*K10))

Thank you for your help and let me know if you need more info.

Discussion is locked

- Collapse -
Brackets.
Sep 16, 2010 5:53AM PDT

I haven't gone through the formula because I am not on my system that has Excel at the moment, and besides, it wouldn't work without knowing the other data.

But in general;

* I assume that the first expression, " IF(ISBLANK(#REF!) " is intended? I know we can use ISERRORs to manipulate functions, but sometimes that #Ref! can mean an error in the formula.

* Excel only allows up to 7 IF statements in any formula. I don't know if that applies to Excel 2007 and 2010, but certainly prior versions. Your formula is OK though.

* Every IF function must have an open bracket and a closing bracket. So nested IF functions can be difficult to keep track of because they must be closed. That often means a number of close brackets at the end of the formula. Yours only shows 2 close brackets at the end.

Examples

=IF(C1=10,"CORRECT",IF(C10=5,"CLOSE",IF(C10=15,"TOO HIGH",IF(C10=11,"ONE OUT","INCORRECT"))))

I have 4 IF statements, of which 3 are nested. I need 4 closing brackets at the end.

* IF means, If this, then that, else something else.

I'm sure you know that already, but looking at your formula I can't see that this is following that rule. I broke it down like this;

a) =IF(ISBLANK(#REF!),

b) IF(ISNUMBER(I10),

c) IF(N10>=I10,N10*L10,N10*K10),N10*K10),

d) IF(ISNUMBER(I10),

e) IF(N10>=I10,N10*L10,N10*K10),N10*K10))


Doesn't c) end the function? that is, If N10 is greater than or = I10, then N10xL10, else, then N10xK10. You also have another 'Else' after that in N10*K10.

If there is an Else somewhere in the expression and that is not another IF statement, then the formula may end prematurely.

Some things for you to look into.

Mark

- Collapse -
In such cases ...
Sep 16, 2010 6:02AM PDT

I tend to use the 'divide and conquer' principle: add a few columns with intermediate results I can check and finally a rather simple formula (easy to check also) to get the final result.
Then I hide the columns with the intermediate results and am happy that I solved the problem.

Kees