Solved

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

Posted on 2011-09-06
474 Views
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
Question by:Jason C. Levine
• 8
• 8
• 3
• +3

LVL 92

Assisted Solution

Patrick Matthews earned 145 total points
ID: 36490045
If each succesive interval is 10...

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

LVL 24

Expert Comment

ID: 36490047
Don't think I understand. Do you mean if A1 is between 1 and 20 you want 200 to appear in B1?
0

LVL 92

Expert Comment

ID: 36490049
If all your intervals were the same length, it would be easy :)
0

LVL 45

Expert Comment

ID: 36490071
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

LVL 70

Author Comment

ID: 36490125
@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

LVL 45

Accepted Solution

aikimark earned 160 total points
ID: 36490183
``````=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.
0

LVL 45

Expert Comment

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

Points should go to Patrick, though.
0

LVL 69

Assisted Solution

Qlemo earned 50 total points
ID: 36490199
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

LVL 45

Expert Comment

ID: 36490206
@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

LVL 39

Assisted Solution

nutsch earned 145 total points
ID: 36490214
=200+CEILING(MAX(A1-20,0),10)*10

T
0

LVL 70

Author Comment

ID: 36490410
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

LVL 70

Author Closing Comment

ID: 36490855
w00t

Thank you, all.
0

LVL 70

Author Comment

ID: 36490864
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

LVL 45

Expert Comment

ID: 36491529
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

LVL 70

Author Comment

ID: 36491553
>> 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

LVL 92

Expert Comment

ID: 36491771
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

LVL 70

Author Comment

ID: 36491817
>> 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

LVL 45

Expert Comment

ID: 36491918
Of course, Patrick's code would never produce invalid values if a negative value were encountered.
0

LVL 69

Expert Comment

ID: 36492108
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

LVL 45

Expert Comment

ID: 36494608
@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

LVL 70

Author Comment

ID: 36496108
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

LVL 45

Expert Comment

ID: 36496616
So a return isn't a negative value?
0

LVL 70

Author Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦

#### 777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.