formula to calculate %- differences for different categories, gender and age intervals

[Masun]

Hi all,

I have a data table that contains some values. I need to calculate the %-differences per category, gender and age interval. I have 3 categories (AA;BB;CC), gender (F;M) and age interval (0-34;35-44;45-54;55-). Please see attached file for a better description.

Is there a smart formula that can accomplish this? Hope someone can help me.

/Masun

[pepe74287]

Hi,

Solution enclosed. However, I would recommend you not to use merged cells unless you really have to.
In this particular case I used function offset as workaround, but it only complicates whole formula.

[Glenn Kennedy]

Pepe74287... there may be a mistake somewhere, as your solution did not give the result anticipated by the OP.

Here's a bit of a monster, based on SUMIFS (I'm not awke yet, so there's bound to be a neater way of doing this).

BtW, I killed the dreaded merged cells, to keep the formulas simpler.

[pepe74287]

Glenn - Right, looks like I didn't pay attention.. Thanks for pointing this out. Enclosed is updated version.

[Glenn Kennedy]

At least we get the same answers now!!

[samba_ravi]

Please remove leading spaces from Range K4 to K7
=IFERROR(SUMPRODUCT((LOOKUP(Tabell4[Age],LEFT($K$4:$K$7,FIND("-",$K$4:$K$7)-1)+0,$K$4:$K$7)=$K4)*(Tabell4[Gender]=L$3)*(Tabell4[Category]=LOOKUP("zzzzzzzz",$L$2:L$2,$L$2:L$2))*(Tabell4[[New Sal]:[New Sal Extra]]-Tabell4[[Old sal]:[Old sal extra]]))/SUMPRODUCT((LOOKUP(Tabell4[Age],LEFT($K$4:$K$7,FIND("-",$K$4:$K$7)-1)+0,$K$4:$K$7)=$K4)*(Tabell4[Gender]=L$3)*(Tabell4[Category]=LOOKUP("zzzzzzzz",$L$2:L$2,$L$2:L$2))*(Tabell4[[Old sal]:[Old sal extra]])),"")
Try this and copy across
See the attached file

[Masun]

Hi,

Wow. Thank you all!

Pepe...Im not that familiar with the sumproduct function. How would it look if the categories are in separate cells and not merged? Please see attached. Would appreciate if you could show me.

Once again...thanks!

/M

[pepe74287]

You would simply replace this part "IF(ISBLANK(L$2),OFFSET(L$2,0,-1),L$2)" in formula with "L$2" and that's it.

Let's say you merge cells L2&M2 into one. If you do that and refer to cell M2 (for *** M), excel consider cell M2 as blank cell. So with cells merged you need to first check whether cells from range L2:Q2 are blank (isblank part) and if yes, you need to take value from adjacent left cell - OFFSET(L$2,0,-1) which is in fact cell M$2.
Hope it's more clear now.

[Masun]

Yep, a little bit at least. I first tried SUMIFS but that formula i constructed turned more and more into a book and i realized that it had to be a better way. You provided one. Thanks!

[pepe74287]

SUMPRODUCT has many advantages over COUNTIFS and SUMIFS - for instance enables you to use logical operators as AND and OR in quite intuitive way, also enables you to work with closed workbooks, it's also good for looking up values with more than one criteria - but also has one disadvantage - even if it's not an array function de jure (doesn't need to be entered with ctrl-shift-enter), de facto it is an array function, so using more SUMPRODUCT in one workbook (especially combined with large ranges) can cause significant slowness in processing.

Anyway, it's really worth to get familiar with it.