Confused

remmargorp

New Member
Joined
Oct 5, 2005
Messages
20
Hey All,

Ive been working on a spreadsheet which involved me working out the rate of a certain object.

The user inputs the Width of a certain object and the Rate is determined by how long it is. Rate = $/metre.

The following code determines the higher the width the higher the price. basiccaly ive referenced the code to another sheet which contains the rates/widths so that the user can change the ratings.

What im stuck in doing is making the cell Null when there is no width imputed. I normally use an if statement like =IF(A1="","",[statement])
but when doing so on this [statement]..

=IF(F8="SS",IF(D8<=Pricing!$B$13,Pricing!$C$13,IF(D8<=Pricing!$B$14,Pricing!$C$14,IF(D8<=Pricing!$B$15,Pricing!$C$15,IF(D8<=Pricing!$B$16,Pricing!$C$16,IF(D8<=Pricing!$B$17,Pricing!$C$17,IF(D8<=Pricing!$B$18,Pricing!$C$18,IF(D8<=Pricing!$B$19,Pricing!$C$19))))))),IF(D8<=Pricing!$B$23,Pricing!$C$23,IF(D8<=Pricing!$B$24,Pricing!$C$24,IF(D8<=Pricing!$B$25,Pricing!$C$25,IF(D8<=Pricing!$B$26,Pricing!$C$26,IF(D8<=Pricing!$B$27,Pricing!$C$27,IF(D8<=Pricing!$B$28,Pricing!$C$28)))))))

it doesnt work and im stuck in why...
PLease help me cause its a project i need done asap :)
thnx in advance

Cheers

Chris
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,590
Hi there
It looks like adding that extra bit of code will exceed the maximum number of nested if statements.
If you have a table of widths on another sheet, why not use VLOOKUP instead, or am I missing something?
regards
Derek
 

remmargorp

New Member
Joined
Oct 5, 2005
Messages
20
Hey,

Ye my first thought was ive exceeded the number of IF statements allowed. Im a very new user so i was proud of my little statement there.
Im not sure how vlookup will help in my situation and i dont really plan on rewriting another statement. Any other ideas on how to allow more IF statements. HAX excel in some way :Phehe

Cheers

Chris
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Maybe this?:
Book1
BCDEFGH
7
82SS
9
10
11
12MetersAmout
1300
1411.5
1524
1636.5
1749
18511.5
19614
20716.5
21819
22921.5
231024
241126.5
251229
261331.5
271434
281536.5
Pricing


Formula in H8 is:
=VLOOKUP(D8,$B$12:$C$28,2,1)

Hope that helps! Post back if that doesn't work for you.
 

Forum statistics

Threads
1,077,780
Messages
5,336,257
Members
399,073
Latest member
fairoos

Some videos you may like

This Week's Hot Topics

Top