Formula for lots of options

acrew

New Member
Joined
Sep 3, 2008
Messages
22
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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})
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top