Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula please
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,I F($C$19:$C$1064<0,IF($AB$19:$AB$10640,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:$A B$10640,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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with formula please
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 "scott23" wrote in message om... 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,I F($C$19:$C$1064<0,IF($AB$ 19:$AB$10640,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$10640,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |