• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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

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?
0
Jason C. Levine
Asked:
Jason C. Levine
  • 8
  • 8
  • 3
  • +3
4 Solutions
 
Patrick MatthewsCommented:
If each succesive interval is 10...

=if(and(a2>0,a2<21),200,if(a2>20,roundup(a2/10)*100,0))
0
 
StephenJRCommented:
Don't think I understand. Do you mean if A1 is between 1 and 20 you want 200 to appear in B1?
0
 
Patrick MatthewsCommented:
If all your intervals were the same length, it would be easy :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
aikimarkCommented:
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.
0
 
Jason C. LevineNo oneAuthor 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
0
 
aikimarkCommented:
=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.
0
 
aikimarkCommented:
I did supply a zero for the Roundup() second parameter.

Points should go to Patrick, though.
0
 
QlemoC++ DeveloperCommented:
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))
0
 
aikimarkCommented:
@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.
0
 
nutschCommented:
How about
=200+CEILING(MAX(A1-20,0),10)*10

T
0
 
Jason C. LevineNo oneAuthor 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.
0
 
Jason C. LevineNo oneAuthor Commented:
w00t

Thank you, all.
0
 
Jason C. LevineNo oneAuthor 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.
0
 
aikimarkCommented:
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.
0
 
Jason C. LevineNo oneAuthor 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.  
0
 
Patrick MatthewsCommented:
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.
0
 
Jason C. LevineNo oneAuthor 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 :)
0
 
aikimarkCommented:
Of course, Patrick's code would never produce invalid values if a negative value were encountered.
0
 
QlemoC++ DeveloperCommented:
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.
0
 
aikimarkCommented:
@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.
0
 
Jason C. LevineNo oneAuthor 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.  
0
 
aikimarkCommented:
So a return isn't a negative value?
0
 
Jason C. LevineNo oneAuthor Commented:
There can be no returns.  Once the user commits to the subscription, no refunds and no backing out.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 8
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now