Excel

chrissytan

New Member
Joined
Dec 9, 2010
Messages
7
I have different items that different suppliers can supply I need to excel to calculate the cheapest price. Each supplier has an invoice price then next to it a rebated price.
I can work out the cheapest Rebated price with the formula =Min(B7:AA7)
but to work out a formula to calculate the corresponding invoice price I have in the past used the formula
=IF(AB7=C7,B7,(IF(AB7=E7,D7,(IF(AB7=G7,F7,(IF(AB7=I7,H7,(IF(AB7=K7,J7,(IF(AB7=M7,L7,(IF(AB7=O7,N7,(IF(AB7=Q7,P7,(IF(AB7=S7,R7,(IF(AB7=U7,T7,(IF(AB7=W7,V7,(IF(AB7=Y7,X7,(IF(AB7=AA7,Z7,"")))))))))))))))))))))))))
in the past it has worked but with this particular spreadsheet it seems to have too many cells to calculate as when I try and put this formula in an Error comes up
"specified formula cannot be entered as it uses more levels of nesting than are allowed in current file format"
What format should I save to or how can overcome this problem.
if anyone can understand my ramblings :confused:
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
In older versions of Excel, such as 2003, you are limited to 7 nested IF statements, and your formula has many more.

There are other ways of doing what I think you are doing.

First, a question.
Is it POSSIBLE that AB7 could equal B7, or D7, or G7, and so on ?
If that's NOT possible, then it looks like you are basically looking for the first match to AB7 in alternate columns, and returning the value of the cell to the left of wherever that match is found, and this will be achievable using something like INDEX and MATCH.
 

chrissytan

New Member
Joined
Dec 9, 2010
Messages
7
Thanks as I am not excel savvy at all, could you suggest how I would use Match or Index to calculate this.
Thanks.
 

tfaulkes

Board Regular
Joined
Jun 4, 2009
Messages
74
Hey Chris,

You could use something like this:

OFFSET(A7,0,MATCH(AB7,C7:AA7,0))
 

Forum statistics

Threads
1,141,217
Messages
5,705,071
Members
421,377
Latest member
FerdiFuchs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top