Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 3
  • +3
23 Comments
 
LVL 93

Assisted Solution

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

Expert Comment

by:Patrick Matthews
ID: 36490049
If all your intervals were the same length, it would be easy :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

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 46

Accepted Solution

by:
aikimark earned 640 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 46

Expert Comment

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

Points should go to Patrick, though.
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 200 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 46

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 580 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
 
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 46

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 93

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 46

Expert Comment

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

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 46

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 46

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

604 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