Problem with multiple If-Clause

[#1]

Hey there,

I'm trying to integrate an If-Clause, which groups my days (1 till 31) into 2.

The reason is that there are transactions two times a month. Therefore it should be like this:

Day 1-4 and Day 20-31 = Transaction on Day 5

and

Day 5-19= Transaction on Day 20

Since I don't want to group them by typing each number manually, like:

If(Day=1, '5',

If(Day=2, '5',

If(Day=3, '5'...))) as GroupedDays

Cause it's simply to long-winded!

My idea was this:

If(Day>=5, If(Day<20, '20',

If(Day>=20,If(Day<5,'5',))))as TestDay

Synthax check is OK.

The problem I'm having here's that my new column just includes 20, not 5.  Although the link towards 'day' is particulary  working.

Means:

When I select 20 in 'TestDay' I get 5-19, which is correct.

But where's my 5 in 'TestDay', any clue?

Thanks

[pat_agen]

hi,

load a mapping table and use applymap. Look in help and on forum for examples. (might be long-winded but you will learn for the future and save yourself time!)

You will have to write out 15 of the days followed by a comma followed by 20. But xl has many useful functionalities to help you do this like copy/paste. the other 16 days just let the default parameter in the applymap() function do that for you.

to get you on your way here is a link to Steve Dark's blog with a post about applymap.

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

[erichshiino · Accepted solution]

I think you should go for the applymap as mentioned in the other post or use intervalmatch.

If you want to keep the if-statement, you can make it simple:

If(Day>=5 and Day<=19, 20, 5)

Hope this helps,

Erich

[#4]

thx