Help with formula please

[scott23]

Hi,
Im trying to calculate the'average win' of a large table of data with
nearly 15 different columns of various information. The rows are dates
in chronological order. I have a formula that checks to make sure it
is passing all the criteria before summing up the win column (AB).
Then it is divided by total number of rows used to calculate the
numerator. The most important part is that if the numerator has even
1 false, it will just provide a '0' as the result of the formula.

The following formula was working fine until i added 1 additional
criteria to both the numerator and denominator. In its current form
it will not accept this below.

=SUM(IF($G$19:$G$1064>=A$2,IF($G$19:$G$1064<=A$3,IF($C$19:$C$1064<>0,IF($AB$19:$AB$1064>0,IF($B$19:$B$1064=C3,
$AB$19:$AB$1064/SUM(IF($G$19:$G$1064>=A$2,IF($G$19:$G$1064<=A$3,IF($C$19:$C$1064<>0,IF($B$19:$B$1064=C3,IF($AB$19:$AB$1064>0,1,0),0)))))))


When i remove 1 of the if statements from the numerator and 1 from the
denominator , it seems to work correctly. Is their a limitation to
the number of if statements you can use ?

Also , does anyone have a suggestion to make my worksheet quicker ? I
have 10 different products that i do this test as well as similar
other ones with multiple timeframes daily/weekly/monthly ...and its
getting very slow.

 

[Trevor Shuttleworth]

Yes, seven

From the Excel Help on the If Worksheet Function:

:
Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. See the following last example.
:

Use the Key word: IF_worksheet_function

Regards

Trevor

Hi,
Im trying to calculate the'average win' of a large table of data with
nearly 15 different columns of various information. The rows are dates
in chronological order. I have a formula that checks to make sure it
is passing all the criteria before summing up the win column (AB).
Then it is divided by total number of rows used to calculate the
numerator. The most important part is that if the numerator has even
1 false, it will just provide a '0' as the result of the formula.

The following formula was working fine until i added 1 additional
criteria to both the numerator and denominator. In its current form
it will not accept this below.

$AB$19:$AB$1064/SUM(IF($G$19:$G$1064>=A$2,IF($G$19:$G$1064<=A$3,IF($C$19:$C$