# Set value in cell based on multiple ranges - has to be an easier way aside from nested IFs

Jason C. Levine used Ask the Experts™
on
Howdy folks,

I have a column with a range of values (1 to 130 or so) and I need to base a second column value on breakdowns within the range.  For instance:

1-20 = 200
21-30 = 300
etc

What formula or function do I use for this?
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
If each succesive interval is 10...

=if(and(a2>0,a2<21),200,if(a2>20,roundup(a2/10)*100,0))

Commented:
Don't think I understand. Do you mean if A1 is between 1 and 20 you want 200 to appear in B1?
Top Expert 2010

Commented:
If all your intervals were the same length, it would be easy :)
Top Expert 2014

Commented:
I think Patrick is on to the same algorithmic/formulaic approach I was going to post.

@jason,

Please post a workbook with an example of the ranges.
Don't talk to me.

Commented:
@Patrick

Yeah.  The system is the first twenty items purchased are \$200, then \$100 for every ten after than.

Your formula returns an error in Excel 2003/2010 about too few arguments and points to the ROUNDUP function specifically.

@Mark

Uh, ok.  Not all that complicated of a sample, you can use any values between 1 and 130...

Test.xls
Top Expert 2014
Commented:
``````=IF(AND(A1>0,A1<21),200,IF(A1>20,ROUNDUP(A1/10,0)*100,0))
``````

You might have started on row 1 instead of row 2.
Top Expert 2014

Commented:
I did supply a zero for the Roundup() second parameter.

Points should go to Patrick, though.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
The roundup function needs a number of decimals, so the formula is
=if(and(a2>0,a2<21),200,if(a2>20,roundup(a2/10,0)*100,0))
Top Expert 2014

Commented:
@jason

I only needed to see a workbook if you had a table (or range of cells) with lower and upper range values for your new value conversion.  Your clarification about the first range and subsequent ranges obviates the need for such a table.
Top Expert 2008
Commented:
=200+CEILING(MAX(A1-20,0),10)*10

T
Don't talk to me.

Commented:
Got it. Thanks, gents. In a meeting for the next hour, will close and award poinks as soon as I'm out and can test.
Don't talk to me.

Commented:
w00t

Thank you, all.
Don't talk to me.

Commented:
For the record:

aikimark's solution is cut-n-paste correct but he and matthewspatrick basically had the same thought.  Qlemo caught the typo and nutsch's solution is also cut-n-paste ready.  Thank you all...saves me from writing an obnoxious SQL query.
Top Expert 2014

Commented:
I did a copy/paste from Patrick, supplying the missing parameter.  That level of work didn't rise to points worthiness.  If there are a lot of these, I suspect that the nutsch solution would perform better.
Don't talk to me.

Commented:
>> That level of work didn't rise to points worthiness.

I disagree.  If I were able to puzzle it out for myself, then Patrick and nutsch would have gotten all the points.  But you saved me who knows how long trying to troubleshoot something I have no experience with.
Top Expert 2010

Commented:
Glad to have helped, and that Qlemo and aikimark were around to correct my misplaced argument for ROUNDUP.  That's what I get, I guess, trying to answer questions while waiting in the lunch queue at the cafeteria :)

Really liked Thomas's suggestion, BTW, as long as we can assume that the minimum result will be 200.
Don't talk to me.

Commented:
>> Really liked Thomas's suggestion, BTW, as long as we can assume that the minimum result will be 200.

Yes.  If the customer only chooses one item, they pay the same as someone who chooses 20.  So Thomas's would work.  I'm not in a position to judge elegance though :)
Top Expert 2014

Commented:
Of course, Patrick's code would never produce invalid values if a negative value were encountered.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Thomas' formula would neither, since   max(A1-20, 0)   will give nothing less then 0. Even with a negative A1 value the formula would not go below 200.
Top Expert 2014

Commented:
@Qlemo

>>Even with a negative A1 value the formula would not go below 200.

If the criteria/expectation for the lowest numeric range is 1-20, then a formula that returns 200 for a zero or negative value might not be in line with expectations.

Jason didn't state what the formula should return in such circumstances.  And we, as the Excel experts, might not have given him a complete answer.
Don't talk to me.

Commented:
The list is an export from an online "orders" table (not really, but good enough for discussion) so only positive values will ever be seen.
Top Expert 2014

Commented:
So a return isn't a negative value?
Don't talk to me.

Commented:
There can be no returns.  Once the user commits to the subscription, no refunds and no backing out.

Do more with