Hello,
I am dealing with a problem and I just can't figure out what to do.
I want to calculate an average of time I need for a task in a bunch of projects (=Hilftabelle!G16-G24).
I have the following formula, which just works fine (as a example for the first three rows):
Formula:
=(1/3)*(IF(OR(G2=Hilftabelle!G16;G2=Hilftabelle!G17;G2=Hilftabelle!G18;G2=Hilftabelle!G19;G2=Hilftabelle!G20;G2=Hilftabelle!G21;G2=Hilftabelle!G22;G2=Hilftabelle!G23;G2=Hilftabelle!G24);E2/F2)
+IF(OR(G3=Hilftabelle!G16;G3=Hilftabelle!G17;G3=Hilftabelle!G18;G3=Hilftabelle!G19;G3=Hilftabelle!G20;G3=Hilftabelle!G21;G3=Hilftabelle!G22;G3=Hilftabelle!G23;G3=Hilftabelle!G24);E3/F3)
+IF(OR(G4=Hilftabelle!G16;G4=Hilftabelle!G17;G4=Hilftabelle!G18;G4=Hilftabelle!G19;G4=Hilftabelle!G20;G4=Hilftabelle!G21;G4=Hilftabelle!G22;G4=Hilftabelle!G23;G4=Hilftabelle!G24);E4/F4)
+...
So, the IF/OR function checks if in the cell G2 (G3, G4) is a certain value (Hilftabelle!G16-24), namely one of the projects I examine. Of course there (in cell G2, G3 etc.) could be the name of other projects, which i do not want to include. Afterwards it devides the time (E2-E4, for example 1:00:00) with the number of task (F2-F4, for example 2).
For example:
Hilftabelle!G16= ProjectA
E2 = 1:00:00 (Time spend for the Project)
F2 = 2 (Tasks done in that time)
Result = 0:30:00 (average time for a task).
NOW MY PROBLEM:
I want to extend this formula for a range from G2 (&E2,F2) just untill G999 (&G999,G999).
Of course i could just copy and paste the formular for 999 times, but this is not really a time-saving method.
What is important: When there are no values or values other than Hilftabelle!G16-24 (which is the values for the projects i want to consider) the formula should not include them.
I hope the problem gets clear,
Thank you very much in advance!
Wabisch
Bookmarks