If Formula

[tmk0427]

I'm trying to create a formula that goes like =if(b4 is between 0 and 10000, b5*b4, if(b4 is between 10001 and 20000, b6*b4, if(b4 is between 20001 and 30000, b7*b4 etc...

Please advise.

Thanks.

 

[Oaktree]

=B4*OFFSET(B5,INT((B4-1)/10000),0)

 

[Scott Huish]

Perhaps:

=INDEX($B$5:$B$7,-INT(-($B$4/10000)))*B4

 

[barry houdini]

Possibly

=INDEX(B:B,CEILING(B4/10000,1)+4)*B4

 

[tmk0427]

IF Formula Clarification

Here's how my spreadsheet looks: I'm trying to get a result in cell A1 that shows =if(b4 is between 0 and 10,000, c4*b4, if(b4 is between 10,001 and 20,000, d4*b4, if(b4 is between 20,001 and 30,000, e4*b4).

b4
10000
c4
2%
d4
4%
e4
6%

I hope this amply clarifies my need. Thanks again.

 

[Scott Huish]

That's a different range than you originally posted, but perhaps:

=INDEX(C4:IV4,-INT(-(B4/10000)))*B4

 

[tmk0427]

That worked. Thanks.