How to replace 11 IFs with a shorter Excel Formula.

[James Perry]

I need to do some basic accounting using an Excel formula.
Its like this:
Cell B20 is labelled.....Profit_Margin_Slab
Cell B21 has..................0% - 5%
Cell B22 .....................>5% - =<10%
Cell B23 ....................>10% - <15%
Cell B24 .................=>15% - =<20%
Cells C20 thru' cell C31 contain the names of 11 products.
Cell D20:D31 contain the sales_price of these 11 products..
Different products will take on a different profit_margin %.
Firstly, i need to match which product gets what %_slab of profit_margin.
Typically, an Excel formula with 11 IFs works well.
My question is: Can a formula with 11 IFs be replaced with a shorter Excel formula.

Many thanks friends for all the time & effort that goes into all this. That's noble!

 

[kchiba]

Hi James,

without seeing the actual worksheet it is difficult to give an answer, but you can try the following.

I imagine that you can put the Slabs in a table with names or each slab and use a VLOOKUP to use the required slab and the same with the products.

So you will have VLOOKUP()*VLOOKUP

 

[NARAYANK991]

Hi James ,

I do not understand what the 11 IFs are doing in one formula ; can you explain what you are trying to calculate , and what are the inputs ?

If you are trying to calculate the figure for one product at a time , why do you need 11 IFs in one formula ?

Narayan

 

[Hui]

James
Posting a sample file with the actual formula and some data would be a great help