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

Jason C. Levine
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
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))
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 :)
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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.
Jason C. LevineDon't talk to me.

Author

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))

Open in new window


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.
Qlemo"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:
How about
=200+CEILING(MAX(A1-20,0),10)*10

T
Jason C. LevineDon't talk to me.

Author

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.
Jason C. LevineDon't talk to me.

Author

Commented:
w00t

Thank you, all.
Jason C. LevineDon't talk to me.

Author

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.
Jason C. LevineDon't talk to me.

Author

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.
Jason C. LevineDon't talk to me.

Author

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.
Qlemo"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.
Jason C. LevineDon't talk to me.

Author

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?
Jason C. LevineDon't talk to me.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial