Help with formula (conditional list)

[jg70124]

I'm trying to get the sum of a set of cells, where the set is
determined by the result of a calculation.

Here's what I want to do:
- If 0 <= a1 < 30, return 0
- If 30 <= a1 < 60, return sum(d2)
- If 60 <= a1 < 90, return sum(c2:d2)
- If 90 <= a1 < 120, return (b2:d2)
- If 120 <=a1 < 150, return (a2:d2)

Of course I can do this with a bunch of nested if statements, but I
wondered if there was a more elegant way to do it.

Thanks.

 

[Dana DeLouis]

How about...

=IF(OR(A1<30,A1>=150),0,SUM(OFFSET(D2,0,0,1,-INT(A1/30))))

 

[Earl Kiosterud]

jg,

You can do this with a lookup table, and a VLOOKUP. If you're not using a non-proportional
font like Courier in your newsreader, this table will be skewed.

A B
5 0 0
6 30 =D2
7 60 =SUM(C22)
8 90 =SUM(B22)
9 120 =SUM(A22)
10 150

For the result:
=VLOOKUP(A1,A5:B10,2)

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Bob Phillips]

I would think that any other solution would be overly-contrived, due to the
non-linearity of the ranges being summed.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Oops, there is a linearity, as Dana shows. Oh well.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jg70124]

How about...

=IF(OR(A1<30,A1>=150),0,SUM(OFFSET(D2,0,0,1,-INT(A1/30))))

Dana - Excellent.

For some reason the first condition 0 was stumping me - the if
statement is a great solution. And I was using
indirect(address(offset))), so just using the width parameter is
another great tip.

J