Can you delay a calculation until required data is entered?

[jkmclean]

When the data is entered in c,d and e in sheet 3 it immediatly updates the average column on the collation sheet.
Is there a way to delay the update where the 114.833 value is until the data entry is complete for the three entries?
As soon as the 104 is entered it changes the 114.833 to 132.1667.
The 174 is calculated based on the 132 instead of the 114.

[TABLE="width: 578"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]hcp[/TD]
[TD]HGS[/TD]
[TD]HSS[/TD]
[TD]HGH[/TD]
[TD]HSH[/TD]
[TD]gms[/TD]
[TD]Ave[/TD]
[TD]Total Pins[/TD]
[/TR]
[TR]
[TD]Betty McLean[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]216.2[/TD]
[TD="align: right"]611.200[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]114.833[/TD]
[TD]689[/TD]
[/TR]
[TR]
[TD]Jim Karasimos[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]223.7[/TD]
[TD="align: right"]603.600[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]117[/TD]
[TD]702[/TD]
[/TR]
[TR]
[TD]Jack StevenHaagen[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]225.4[/TD]
[TD="align: right"]608.500[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]115.833[/TD]
[TD]695[/TD]
[/TR]
[TR]
[TD]John McLean[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]584[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]665.000[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]180.5[/TD]
[TD]1083[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Verna Mortensen[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]222.3[/TD]
[TD="align: right"]622.800[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]146.833[/TD]
[TD]881[/TD]
[/TR]
[TR]
[TD]Ken Killen[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]573[/TD]
[TD="align: right"]246.8[/TD]
[TD="align: right"]643.200[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]184.833[/TD]
[TD]1109[/TD]
[/TR]
[TR]
[TD]Joyce Banks-Stevenhaagen[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]234.1[/TD]
[TD="align: right"]611.300[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]140.5[/TD]
[TD]843[/TD]
[/TR]
[TR]
[TD]Alex McKinnon[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]289[/TD]
[TD="align: right"]203.6[/TD]
[TD="align: right"]596.800[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]96.3333[/TD]
[TD]289[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Linda Carter[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]377[/TD]
[TD="align: right"]218.5[/TD]
[TD="align: right"]606.500[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]112[/TD]
[TD]672[/TD]
[/TR]
[TR]
[TD]Patricia Renaud[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]374[/TD]
[TD="align: right"]235.1[/TD]
[TD="align: right"]606.200[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]121.167[/TD]
[TD]727[/TD]
[/TR]
[TR]
[TD]Dan Murphy[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]230.6[/TD]
[TD="align: right"]645.800[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]106[/TD]
[TD]636[/TD]
[/TR]
[TR]
[TD]Evelyn Farrah[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]359[/TD]
[TD="align: right"]218.9[/TD]
[TD="align: right"]604.700[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]112.333[/TD]
[TD]674[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gerry Revelle[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]469[/TD]
[TD="align: right"]250.6[/TD]
[TD="align: right"]614.800[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]149.333[/TD]
[TD]896[/TD]
[/TR]
</tbody>[/TABLE]


From Sheet 3
[TABLE="width: 1047"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD]Hits & Misses[/TD]
[TD][/TD]
[TD]Game 1[/TD]
[TD]Game 2[/TD]
[TD]Game 3[/TD]
[TD]Team Series Scratch[/TD]
[TD]Team SeriesHcp[/TD]
[TD]Team Game[/TD]
[TD]Team Game Hcp[/TD]
[TD]Game1Hcp[/TD]
[TD]Game2Hcp[/TD]
[TD]Game3Hcp[/TD]
[TD]# games[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Betty McLean[/TD]
[TD][/TD]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]174.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]132.1667[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

I'm not sure what row you are on in Sheet 3, but you can adjust as necessary...

Try something like this;

=IF(OR(C2="",D2="",E2=""),"",AVERAGE(C2:E2))

 

[hrayani]

another way

=IF(COUNTA(C2:E2)=3,AVERAGE(C2:E2),"")

 

[jkmclean]

The cell with the value of 114 is on the collation sheet and it updates immediately after entering 104 on sheet 3.
the j13 on the collation sheet is calculated by dividing the total pins by the number of games. K13/i13 on the collation sheet.
When I enter the 104 value on sheet3 the collation sheet is updated with a value of 113.
Total pins is calculated with the following
=SUMIF(Sheet3!A$3:A21720,C13,(Sheet3!C$3:C21720)) +SUMIF(Sheet3!A$3:A21720,C13,(Sheet3!D$3:D21720)) +SUMIF(Sheet3!A$3:A21720,C13,(Sheet3!E$3:E21720)) +SUMIF(Sheet3!R$3:R21720,C13,(Sheet3!T$3:T21720)) +SUMIF(Sheet3!R$3:R21720,C13,(Sheet3!U$3:U21720)) +SUMIF(Sheet3!R$3:R21720,C13,(Sheet3!V$3:V21720))
I need some way to pick up the value from j13 and use it to enter and calculate the values on sheet3.
game1, game2 game3 with hcp on sheet3 (174.3 value)is calculated by
=IF(C126="","",(((210-INT(INDEX(Collation!$J$13:$J$59,MATCH($A126,Collation!$C$13:$C$59,0))))*0.9)+C126))

[TABLE="width: 578"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]hcp[/TD]
[TD]HGS[/TD]
[TD]HSS[/TD]
[TD]HGH[/TD]
[TD]HSH[/TD]
[TD]gms[/TD]
[TD]Ave[/TD]
[TD]Total Pins[/TD]
[/TR]
[TR]
[TD]Betty McLean[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]216.2[/TD]
[TD="align: right"]613.900[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]113.286[/TD]
[TD]793[/TD]
[/TR]
</tbody>[/TABLE]