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

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

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

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.

@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

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

Points should go to Patrick, though.

@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.

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.

w00t

Thank you, all.

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.

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.

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

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.

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

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

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.

@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.

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.  

So a return isn't a negative value?

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