# Formula for lots of options

#### acrew

##### New Member
Hi
I'm trying to put together a formula that will report back on only one option out of four when valid.

1. If F15 is between -0.01 and -25000 result should be 1500
2. If F15 is between -25001 and 50000 should be 1000
3. If F15 is between -50001 and 100000 should be 750
and
4. if F15 is 100000+ = 500

I also wanted it to report 0 if F15 equals 0 or is a +ve number so started the formula with IF(F\$15>=0,"",IF.....

Thing is I have four option lines.
So in line one I only want it to return the 1500 if it is less than 0 but not more than -25000 inclusive.
If it's more than -25000 than I need it to show 0 in this line and then put the correct result according to option 2 - 4..

Hope this makes sense.
I thought this was an easy one but just cannot seem to get it to work.

Thanks

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

##### Well-known Member
think you have to use both IF and AND functions.

#### nbrcrunch

##### Well-known Member
options 2 & 3 share a span of values together.

#### mikerickson

##### MrExcel MVP
It looks like you post is missing some negative signs.

If this describes the results you want

-100,000 <= F15 <= -50,001 return 750
-50,000 <= F15 <= -25,001 return 1000
-25,000 <= F15 < 0 return 1500
0 <= F15 return 0

this LOOKUP formula will do what you want
=LOOKUP(F15,{-100000, -50000, -25000, 0}, {750,1000,1500,0})

#### acrew

##### New Member
Thanks for the formula. Unfortunately it's doing the same thing my one was and is reporting the resulting amount in all four lines. I've pasted a version of what I'm trying to get to so you can see. I have been asked to split this out so that the four options are separated - otherwise the formula would work fine!
So if my result is -5425 it should fall into the first option - which is "do not achieve target by \$1 - \$25K" (these amounts are assuming result is negative so would be negatives).
I need a formula that will calculate out what option it should report back and then put that in ONLY that line - but the formula needs to be in all four options.

<TABLE style="WIDTH: 300pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=399 border=0 x:str><COLGROUP><COL style="WIDTH: 168pt; mso-width-source: userset; mso-width-alt: 8192" width=224><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 168pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=224 height=15>Result</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 61pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=81 x:num="-5424.5497158716898">(5,425)</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=94>Should report back:</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Achieve Target</TD><TD class=xl84 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Currently gives me</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Do not achieve Target by \$1-\$25k</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1500</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1500</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Do not achieve Target by \$25,001-\$50,000</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1500</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Do not achieve Target by \$50,001 - \$100,000</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1500</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl80 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Do not achieve Target by \$100,001+</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1500</TD><TD class=xl78 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR></TBODY></TABLE>

Hope this makes sense.
thanks

#### mikerickson

##### MrExcel MVP
I don't know where these values are, but

=IF(notAcheveTargetValue < 25000,1500,0)

Replies
1
Views
322
Replies
4
Views
242
Replies
0
Views
217
Replies
9
Views
279
Replies
4
Views
128

1,191,031
Messages
5,984,235
Members
439,879
Latest member
KingGoulash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back