IF Statement Help

[Guest]

Hi,

I have a resource spreadsheet that I'd like to automate as much as possible.

In Column "C" I have the supplier the column "E" I have the res rate, column
"F" I have the supplier rate and column "G" I have the charge rate.

Now based on the supplier there is a % mark up and when I do a single IF
statement it works. I'm having trouble linking them into a nested statement.
I would also like it to be a column based statement rather than a cell one..
is this possible?

This one works on it's own:
=IF(C77="Supplier1", (D77*11.11%)+D77, "0")

This is my nested one which doesn't work. (it's also cell based)
=IF(C77="Supplier1", (D77*11.11%)+D77,
(=if(C77="Supplier2",(D77*13.635%)+D77, (=if(C77="Supplier3",
(d77*5.26%)+D77, (=if(c77="Supplier4", (D77*0.02%)+D77, (=if(c77="Supplier5",
(d77*0.02%)+d77, "Nill")))))))))

Could someone help or give some tips.

Thank you.
Tracey

 

[JE McGimpsey]

First, you don't use "=" when you nest functions, so:

=IF(C77="Supplier1", D77*(1 + 11.11%), IF(C77="Supplier2", D77*(1 +
13.635%), ...

but this is really where a lookup should be done. In a separate part of
the worksheet (or on a second worksheet, which could be hidden if
desired), enter:

J k
1 Supplier1 11.11%
2 Supplier2 13.635%
3 Supplier3 5.26%
4 Supplier4 0.02%
5 Supplier5 0.02%


Then use something like this function:

=IF(ISNA(MATCH(C77, J:J, FALSE)), "Nill", D77 * (1 + VLOOKUP(C77,
J:K, 2, FALSE)))

 

[Guest]

Thank you!

First, you don't use "=" when you nest functions, so:

=IF(C77="Supplier1", D77*(1 + 11.11%), IF(C77="Supplier2", D77*(1 +
13.635%), ...

but this is really where a lookup should be done. In a separate part of
the worksheet (or on a second worksheet, which could be hidden if
desired), enter:

J k
1 Supplier1 11.11%
2 Supplier2 13.635%
3 Supplier3 5.26%
4 Supplier4 0.02%
5 Supplier5 0.02%


Then use something like this function:

=IF(ISNA(MATCH(C77, J:J, FALSE)), "Nill", D77 * (1 + VLOOKUP(C77,
J:K, 2, FALSE)))