# Automatic display average when ready

#### wiwchar

##### Board Regular
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
ABCDE
1Date5-Feb-055-Feb-056-Feb-057-Feb-05
2Time8:08 AM1:00 PM9:07 AM10:11 AM
3Serial No123124125126
4User Id243a243a271c244a
5C1256342234257
6C2249283261228
7C3252252298291
8C4263295201301
9C5225288241281
10C6182204204278
11C7264319264220
12C8243237305225
13C9202275267246
14C10272270235228
15C11279274218281
16C12190215288227
17C13249295248222
18C14257312285222
19C15266306289224
20C16253234268274
21C17255282262267
22C18262271302284
23C19233236241296
24C20255246262281
25C21296271286299
26C22250229208264
27C23250220280285
28C24239299241282
29Average
UpperBeam

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

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.

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.

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.

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.

How do I return a null result?

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

Replies
7
Views
109
Replies
5
Views
190
Replies
13
Views
514
Replies
9
Views
108
Replies
1
Views
238

1,196,345
Messages
6,014,730
Members
441,842
Latest member
mattdale

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back