Simplification of IF Statement
 

G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

Simplification of IF Statement
 

Try this

=VLOOKUP(D3,A10:C18,3,TRUE)

Note that Column A must be sorted for this to work (as in your example) and
that despite column B being within the range it's not being used. The formula
simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

Mike

"NoodNutt" wrote:

G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

Simplification of IF Statement
 

simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

It finds the closest match less than. Sorry for the confusion

"Mike H" wrote:

Try this

=VLOOKUP(D3,A10:C18,3,TRUE)

Note that Column A must be sorted for this to work (as in your example) and
that despite column B being within the range it's not being used. The formula
simply looks at column A and as soon as it finds a value that exceed D3 (or
the last value) it returns the value in Column C

Mike

"NoodNutt" wrote:

G'day everyone

I need some advice & help on simplifying the following please.

=IF(D3<=$B$15,$C$15,IF(D3$A$16<$B$16,$C$16,IF(D3 $A$17<$B$17,$C$17,IF(D3$A$18<$B$18,$C$18,IF(D3$A $19<$B$19,$C$19,IF(D3$A$20,$C$20,0))))))

Essentially there is 6 arguments, the only argument that will return a value
is the last, all the others return 0

D3 has a value that is a multiplier of 2 cells to arrive at the value, what
I would like to happen is that once the value updates, is for each of the
arguments to check which cell range that value fits between and return a
corresponding value from another cell.

assume D3 = 100

A15 = 10 & B15 = 20 C15 = 0.0226
A16 = 21 & B15 = 30 C16 = 0.0333 Etc.......

So when it get to

A19 = 91 & B19 = 100 C19 = 1.125

The response I should get in my formula cell is 1.125

Appreciate any assistance.
TIA
Mark.

Simplification of IF Statement
 

G'day Mike

Thank you so much, that hit the spot nicely.

Regards
Mark.

Simplification of IF Statement
 

Glad I could help and thanks for the feedback

"NoodNutt" wrote:

G'day Mike

Thank you so much, that hit the spot nicely.

Regards
Mark.

Simplification of IF Statement
 

Actually Mike

I was working on trish's problem.

I think what I have put together will get her out of trouble, with your help
of course.

Thx again
Mark.