Alternative to multiple nested if statements

**booo** · 10-20-2010, 01:16 PM

Consider the provided spreadsheet. I had to calculate the range of data on 30 days intervals and had to use multiple nested if statements to write the function.

All though it works fine, i was wondering if there was an easier alternative to this method.

Kindly help.

Thanks,
booo

**DonkeyOte** · 10-20-2010, 01:30 PM

Based on your sample

L2:
=FLOOR(MAX(0,K2-1),30)+(K2>30)&"-"&CEILING(MAX(1,K2),30)
copied down

Note: your 180-210 should be 181-210 I believe.

**zbor** · 10-20-2010, 01:32 PM

This might be result:

=30*INT(K2/30)+(K2>30)&"-"&30*INT(K2/30)+30

**MarvinP** · 10-20-2010, 01:38 PM

Hi booo,

I wonder if you can do this in Excel 2003. Find an attached file with a pivot table that allows grouping of your data. Is that what you are looking for? You can change the max, min and group ranges.

Find the answer in both 2003 and 2007 format files.

**booo** · 10-20-2010, 01:41 PM

Thanks Guys,

But it would be great if you could explain those functions as well, so that I would understand and actually know what I am using

Many Thanks

**zbor** · 10-20-2010, 02:13 PM

I'll explain mine which is maybe more intuitive although FLOOR and CEILING will do pritty
much same...

So... I'll divide formula into 4 parts:

=30*INT(K2/30) +(K2>30) &"-"& 30*INT(K2/30)+30

30*INT(K2/30) will made you steps of 30...
For example:
100/30 will give you 3.3333
INT wil take only whole value and ignore decimal places so result will be 3
Now 30 * 3 will give you 90 (which is first step of 30*n below desired value (100)

+(K2>30) needs only to return 0 in first step because all other values end with 1 (11,21,31,41,101...) whil first doesn't start with 1 but with 0.

So it simply say: Is it first occurence? If no add TRUE (equal to 1), if yes add FALSE (equal to 0).

&"-"& is used to combine words. All words you can combine with & but characters must be within " "

Example: ="See this "&92&"-"&A1&78&"example" will return you:

See this 92-78example (if there is nothing in a1... If there is somethig in A1 it will return that value between - and 78)

30*INT(K2/30)+30 is same as first part only adds always 30 at the end instead of 1 or 0 as previous.
you can also use 30*(INT(K2/30)+1) if you like

**MarvinP** · 10-20-2010, 02:29 PM

Hey - Let me explain my answer too...

The problem looked like you wanted to put things into buckets based on their values. I know Excel will do this with Pivot Tables and the Grouping function built into it. It shows the minimun and maximum values and asks how big you want each bucket. Excel does most of this for you!

I find there are functions in Excel most users never know are available to them to use. I was thinking you might looking at Pivot Table Grouping to solve this problem.