Confused

[remmargorp]

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]

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]

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]

Maybe this?:

Book1
	B	C	D	E	F	G	H
7
8			2		SS		4
9
10
11
12	Meters	Amout
13	0	0
14	1	1.5
15	2	4
16	3	6.5
17	4	9
18	5	11.5
19	6	14
20	7	16.5
21	8	19
22	9	21.5
23	10	24
24	11	26.5
25	12	29
26	13	31.5
27	14	34
28	15	36.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.