testing by the hr.ods (23.6 KB)
I am trying to get a formula to calculate a % of 1s or -1s from active cells. I tried multiple ways and E3 and F3 have 2 different ways I tried. The formula works fine as long as row 4 is larger than 6 but I can not get it to work the other way. If 4 and 6 are even it should return 0, so Q3 should be 0 and it isn’t.
All I really need is the sum and the % rows but created the others to get the data for calculations.
Formula in M3:
=IF(M4>M6;(M4/M2);(IF(M6>M4);(M6/M2);0))
should be:
=IF(M4>M6;(M4/M2);IF(M6>M4;(M6/M2);0))
In IF(M6>M4), the close parenthesis is closing the IF.
Remember that function wizard can help to find these issues.
Hallo
=IF(M4=M6 ; 0 ; MAX(M4;M6) / M2 )
mariosv
That worked great. All cells now calculating as expected. Appreciated.
karolus
That also works just as well. Thank you for your input. Is there any advantage to either of these formulas?
mariosv
For some reason the function wizard did not help. I tried it so many ways and I got multiple different error codes but could not sort out what I was doing wrong. I thought it was something with the closes but your explanation is better then the wizard. Thanks
karolus
I will have to read up on that MAX function, interesting solution. Wish I could choose both since both worked. I chose mariosv because his also showed where my error was but your solution is cleaner
Both are fine in the latter case with only 2 values… but imagine you have to pick the largest value from 3 ore more different values!