Summarization

[Mankan]

Hello!

I have a problem with ”summarization”.

See the picture (it’s a minimized example).

I have one tab (tab 2) with result in 8 different categories (the color is dynamic depending on an answer in tab 1).

In tab 3 I want to sum these categories, both with colors and the number of “the worst color”. (Done with a formula and conditional format.)

The formula is kind of:
IF one is RED mark it red, and calculate the amount of red in that category.
ELSE IF one is GREY mark it grey, and calculate the amount of grey in that category.
ELSE IF mark it green, and calculate the amount of green in that category.

This I can do because I have “hidden” numbers (numbers of the same color as the cell) in the result. So 2 is RED, 1 is GREY and 0 is GREEN. Easy to use in formula.


But now I want to sum up the numbers of RED, GREY and GREEN from the total result (to an “end result”).

I can´t find any way to do that… =(
(It has to be done without macron.)

What I have read, I can´t look of what color a cell has (without macron).

I do have an idea of adding (and hiding decimals) 0,2 vs 0,1 vs 0,0 in the total result. And looking for the numbers of “X,2“ with MOD(), but can´t make it work neither.

Do anyone have an idea I would be very grateful!!

Regards
Magnus

 

[MarcelBeug]

Just to proceed on your idea of adding a decimal: suppose .2 is red, so in your example the Total results for red would be 1.2 and 2.2.
Suppose the toals results are in E3:E5, this will give you the totals for red:

=SUMPRODUCT(INT(E3:E5)*(MOD(E3:E5,1)=0.2))

 

[Mankan]

That did the trick!
Thank you MarcelBeug!

If anyone have a better/cleaner solution than my “looking for 0,2” I will be glad for that also!

 

[Mankan]

Hello again!

I get a very strange problem with this formula.
=SUMPRODUCT(INT(E3:E5)*(MOD(E3:E5,1)=0.2))

If I have low numbers like
1,2
3,2
2,2
in column the formula works fine (the result is 6 as I want)

But if I have greater number like 8,2 and above the formula ignore to sum up these… so the numbers
1,2
3,2
8,2
becomes 4… ?!

Why?

 

[MarcelBeug]

It appears to be a rounding error: when I test MOD(8.2, 1), it resolves to something like 0,19999999999.

Solution:

=SUMPRODUCT(INT(E3:E5)*(ROUND(MOD(E3:E5,1),1)=0.2))

 

[Mankan]

Hello!

Very strange! But yes that was the case for me too.

Thank you for finding that!

(Isn’t this a bug that should be reported to MS…)

//Magnus

 

[MarcelBeug]

(Isn’t this a bug that should be reported to MS…)

No, it is well known (that's why it didn't too long to find the cause) and eloborated in e.g. https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel