Automatically fill a cell based on number - Column coloring

Originally Posted by carduel

Google Translator:

Hello everyone!

I'm trying to solve one problem which consists of 3 parts.

1) I writing numeric data into the table (range A2:G9). Data can be positive and negative. In row A10:G10 count the product of all the individual columns. If any cells (A2:A9) have a negative number (2 negative, 3 negative,...,8 negative) product in A10 should be negative (In mathematics product of two negative integers is a positive result). Values that would like to get are marked in yellow.

Thank You All For Help.

Can I question your assumption here. Take B2:B9. All 8 cells are negative and any negative number multiplied by another negative number is a positive. Since there are an even number of negative numbers mathematically speaking the total is a positive +720, not the negative you say it should be.

Hmm that was an interesting problem. I was trying my BEST to avoid having four nested IF statements and came across the ISEVEN formula. Basically when the count of negatives is EVEN you want to multiply by -1.

This formula will work in cell A10, and you can drag it to the right.

Please Login or Register  to view this content.

Essentially I am determining IF the count of negatives is EVEN AND the count of negatives is greater than 1, then multiply the product formula by negative 1, otherwise just multiply by 1.

Then the other conditional formatting is really simple with the following formules.
Range A2:G9 conditional formatting formula:

Please Login or Register  to view this content.

Range A10:G10 conditional formatting formula:

Please Login or Register  to view this content.

Sample workbook with solutions is attached.

Hi-

Here's part of your solution. Paste this into A10 and then copy across:

=IF(COUNTIF(A2:A9,"<0")=0,PRODUCT(A2:A9),-1*ABS(PRODUCT(A2:A9)))

Still looking at the conditional formatting.

Originally Posted by carduel

Thank you very much.

Just wanted to confirm that you are happy that the signage is correct for what you want. See post #2.
Your result is not consistent with the normal mathematical rules that apply to mutiplying a set consisting of an even number of values all of which are negative.

Originally Posted by Richard Buttrey

Just wanted to confirm that you are happy that the signage is correct for what you want. See post #2.
Your result is not consistent with the normal mathematical rules that apply to mutiplying a set consisting of an even number of values all of which are negative.

Yeah, this is an odd request for sure

Originally Posted by Richard Buttrey

Just wanted to confirm that you are happy that the signage is correct for what you want. See post #2.
Your result is not consistent with the normal mathematical rules that apply to mutiplying a set consisting of an even number of values all of which are negative.

A negative number does not affect my calculations and we used only visually... I know that this way opposes mathematical rules but does not affect my result. I believe that my task can be solved by mathematical rules but my level of knowledge is my limit. What I imagined giving me accurate results.

Thank you very much again.