Solved

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

Posted on 2011-09-06
23
456 Views
Last Modified: 2013-11-05
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
Comment
Question by:Jason C. Levine
  • 8
  • 8
  • 3
  • +3
23 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
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

by:StephenJR
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

by:Patrick Matthews
ID: 36490049
If all your intervals were the same length, it would be easy :)
0
 
LVL 45

Expert Comment

by:aikimark
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

by:Jason C. Levine
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

by:
aikimark earned 160 total points
ID: 36490183
=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
 
LVL 45

Expert Comment

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

Points should go to Patrick, though.
0
 
LVL 68

Assisted Solution

by:Qlemo
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

by:aikimark
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

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

T
0
 
LVL 70

Author Comment

by:Jason C. Levine
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 70

Author Closing Comment

by:Jason C. Levine
ID: 36490855
w00t

Thank you, all.
0
 
LVL 70

Author Comment

by:Jason C. Levine
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

by:aikimark
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

by:Jason C. Levine
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

by:Patrick Matthews
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

by:Jason C. Levine
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

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

Expert Comment

by:Qlemo
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

by:aikimark
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

by:Jason C. Levine
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

by:aikimark
ID: 36496616
So a return isn't a negative value?
0
 
LVL 70

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now