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:
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,909
Messages
5,525,581
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top