If statement help

[Edward]

Hi,
I believe this would be categorized as a "nested if" question.

I have to calculate different pricing based on weight categories.
So- in English, it would be:
If cell a1 is greater than 1000 but less than 1999 then multiply a1 times
c4, if A1 is greater is greater than 1999 but less than 2999 then multiply
A1 times C5, etc

Can someone provide an example of that syntax??

Thanks so much.

 

[M Kan]

It sounds like you need a VLOOKUP table, as there is also 0-999, etc.

You can structure the table like this

Col A Col B
row 3 0 C4
row 4 1000 C5
row 5 2000 C6
row 6 3000 C7
row 7 4000 C8

The formula would look like:

VLOOKUP(A1, A3:B7,2)*A1

 

[Sandy Mann]

Try something like:

=A1*CHOOSE(MATCH(A1,{0,1000,2000,3000,4000}),0,C4,C5,C6,C7)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guy Lydig]

If you would like to use a nested If (you cannot nest more than 7 times) you
can try something like this:

If C1 through C4 contains the multipliers and column A contains the numbers
to be checked (if they are less than whatever...), put this formula in B1 or
D1, etc. Then drag down the Fill Handle as far as you like. The ISBLANK
portion prevents 0s from being in the corresponding cells in Column B or D,
etc. if A has no data. Put the entire formula on one line with no spaces.

=IF(ISBLANK(A1),"",IF(A1<1000,A1*C$1,IF(A1<2000,A1*C$2,IF(A1<3000,A1*C$3,A1*C$4))))