Automatic display average when ready

[wiwchar]

I have a spreadsheet like the one below. I would like the cell in row 29 to display the average of rows 5 to 28 when the user places some information in row 4. Can this be an automatic event? As the example sits right now, columns B - E would have the average displayed in row 29. When the user places their id into F4, the average would calculate into F29. The user would most likely enter their id before any info into rows 5 - 28, therefore this would probably need to paste the formula into the current column each time.

Upper Beam SPC sample.XLT
	A	B	C	D	E
1	Date	5-Feb-05	5-Feb-05	6-Feb-05	7-Feb-05
2	Time	8:08 AM	1:00 PM	9:07 AM	10:11 AM
3	Serial No	123	124	125	126
4	User Id	243a	243a	271c	244a
5	C1	256	342	234	257
6	C2	249	283	261	228
7	C3	252	252	298	291
8	C4	263	295	201	301
9	C5	225	288	241	281
10	C6	182	204	204	278
11	C7	264	319	264	220
12	C8	243	237	305	225
13	C9	202	275	267	246
14	C10	272	270	235	228
15	C11	279	274	218	281
16	C12	190	215	288	227
17	C13	249	295	248	222
18	C14	257	312	285	222
19	C15	266	306	289	224
20	C16	253	234	268	274
21	C17	255	282	262	267
22	C18	262	271	302	284
23	C19	233	236	241	296
24	C20	255	246	262	281
25	C21	296	271	286	299
26	C22	250	229	208	264
27	C23	250	220	280	285
28	C24	239	299	241	282
29	Average
UpperBeam

 

[NBVC]

Can you use a simple if statement that will keep the cell blank if no user id is entered?

E.g. =If(A$4="","",Average(A$5:A$28)) and copied over to B4,C4,D4,E4 and F4

 

[wiwchar]

This worked well. Thanks. Now, in addition, I am plotting the average on a line chart that has a line as an upper limit and lower limit that extend for a number of points. The average needs to be plotted as it happens. So, if there are 100 upper limits and 100 lower limits, these would plot across the entire chart and as the averages are calculated, this line would extend. Right now, what I have is the upper and lower limits, but the average plots to where the number is and then goes to zero for the remainder.

Is this possible? Thanks.

 

[NBVC]

Not to sure what you are trying to do. Can you elaborate? Please Show the table you are charting from and exactly which chart you are using.

 

[wiwchar]

The table is like the one above. This example shows data in columns B - E, but as well in rows 99 and 100, I have placed the Upper and Lower limits. These appear across the chart at their constant values. Let's use 225 and 275 as examples. Then the chart will have a line across the entire chart at 225 and one at 275. The average is done on a sample by sample basis. This is where the chart looks goofy, because starting from the column that does not have an average to calculate, the line drops to zero and continues along the x-axis. I would like to have it that if the value of the average is zero, no line will display with the upper and lower limit lines.

I hope I am clearer with this explanation. Thanks for your help.

 

[NBVC]

There may be a couple of options,

You can either return a null result if the average is 0, you will get a break in your average plot, so you will not get the sudden dip to 0.

Or perhaps you can return the result equal to the previous column's result.

Eg. =If(B$4="","",If(Average(B$5:B$28)=0,A$29,Average(B$5:B$28)) copied over to E29. The only problem would be with your first column result. I am not sure if you may want to have some default result if your average is 0.

Hope this helps.

If I totally misunderstood, please clarify where I went wrong in the interpretations.

 

[wiwchar]

How do I return a null result?

 

[NBVC]

=If(B$4="","",If(Average(B$5:B$28)=0,"",Average(B$5:B$28))