Distributing an average across cells

[rmedders88]

[TABLE="width: 500"]
<tbody>[TR]
[TD]read[/TD]
[TD]Desired result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am not sure if this is possible, but I am trying to build a sheet that will spread a total across multiple cells if blank. In the example above column "A" represents an instrument reading that is taken generally 5d/wk and I need something to average in the days if not populated. I have a formula that totals from one read to the next if days are missing, but I can't solve how to build the distribution. Forgive if I use poor terms I am still relatively new to Excel. I can do this manually, but am trying to build something I can lock and that saves staff some time and doesn't allow them to accidentally erase formulas etc in the sheet.

 

[NiMip]

Well, it ain't pretty and could probably be optimised, but the following worked for me. With the "read" heading in cell B2, and starting the formula in C3 and filling down:

=IFERROR(IF(AND(B2<>"",B3<>""),B3-B2,IF(AND(B3<>"",B2=""),C2,(MIN(IF((B:B)>LOOKUP(2,1/($B$3:B3<>""),$B$3:B3),B:B))-LOOKUP(2,1/($B$3:B3<>""),$B$3:B3))/(MATCH(MIN(IF((B:B)>LOOKUP(2,1/($B$3:B3<>""),$B$3:B3),B:B)),B:B,FALSE)-MATCH(LOOKUP(2,1/($B$3:B3<>""),$B$3:B3),B:B,0)))),B3)

Note: This is an array formula and you have to press CONTROL+SHIFT+ENTER for it to work.

 

[StephenCrump]

rmedders88, welcome to the Forum

Another way, assuming your first reading is in in cell A1:

B1: =A1
B2: =IF(A2="",B3,(A2-INDEX(A$1:A$5,MATCH(A2-0.00001,A$1:A$5,1)))/(ROW()-ROW(A$1)+1-MATCH(A2-0.00001,A$1:A$5,1))) Copy down

This will only work if:

- A1 always contains an opening value?
- Your instrument readings always increase, as in the example you've shown?

 

[rmedders88]

I must bow to your greatness! Both of these worked very well. Jedi you both must be Thanks so much!

 

[rmedders88]

Stephen- I didn't answer your question and I see why you asked. The read does not always increase. If the equipment doesn't operate then the read will sometimes be the same so I get an #N/A as result on those days. Would there be a way to add another "If" function to cure it?

 

[StephenCrump]

B2: =A2
B3: =IF(A2="",B2,(INDEX(A3:A$16,MATCH(TRUE,A3:A$16<>"",))-A2)/MATCH(TRUE,A3:A$16<>"",)) Array-entered

Excel 2010
	A	B
1	Read	Result
2	35	35.00
3		-2.50
4	30	-2.50
5		-0.67
6		-0.67
7	28	-0.67
8		19.67
9		19.67
10	87	19.67
11	80	-7.00
12		-2.50
13	75	-2.50
14		8.33
15		8.33
16	100	8.33
Sheet1