Create an average in alternating cells

[99problems]

Hey people, I need a hand here. So, I have values in cells B4 and B6. I need to be able to create an average in cell C5. Problem I’m having is, I need a formula in every C cell due to the nature of input, but I don’t need a trailing average, like I am getting right now in cell C7. Basically, once I do my input B6, I don’t need it to average that number with “0” in C7. What do I do? Thanks in advance for your help.

 

[Joe4]

Welcome to the Board!

I am having a hard time visusally what you are trying to do. Can you provide a sample, along with your expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[99problems]

Excavation Template.xlsx
	A	B	C	D	E	F	G	H
6
7	STA	Alignment	Length	Width	Avg. Width	Depth		Avg. Depth
8	1+00	L/LF		10		4
9			50		15			7
10	1+50	L/LF		20		6
11			50
12	2+00	L/LF		30		8
13			50
14	2+50	L/LF		10		4
15			50
16	3+00	L/LF		20		6
17			50
18	3+50	L/LF		30		8
19			50
20	4+00	L/LF		10		4
21
22
23
LC#5 Unclassified Excavation

 

[99problems]

My hopes is to have anything in column E read the average of the alternating cells of column D, but not have a result show in cell E21, as there is no second value present in D22. BUT, if the values were input a row down (in relation to the above sheet). then the results in E would also shift, without a result in E22, cause there would be no value present in D23. Not sure if that is any more clear, but I hope it helps a little.

 

[Joe4]

Still a bit confusing.
Can you walk us through the examples you posted and tell us exactly how you got that number?

At first, I though the average width in E9 was looking at the row above and below it in column E, so it is averaging D8 and D10, which would give us the 15 that we see in cell E9.

But then I looked at Depth, and the logic I assumed does not seem to hold muster there, as the average of 4 and 7 is 5, not 7.
So I guess I still do not understand the logic you want to use.

Also, are columns F and G merged cells? If so, I STRONGLY suggest getting rid of them. Merged cells are a cancer to Excel and cause ALL sorts of issues!
Better to use the "Center Across Selection" formatting option than to use Merged cells.

 

[99problems]

Your first assumption is correct, as it turns out the sheet is not complete. Sorry about that. At the moment I am working solely on the average width area, trying to find a formula I can paste down the sheet and some other sheets. Working with only average width, the "E" cells are ONLY to read the "D" cells above and below it. I could do the simple "(D8+D10)/2", but the problem with filling that down is that when I have a break after input at "D20", the "E21" will still average with a blank cell. I want to get rid of that blank cell at the end. BUT, I can't simply skip cells, because if my next set of numbers start at row 23, the my odd and even rows will reverse, so I need formulas in every cell.

 

[Joe4]

Place this in cell E9 and copy down.

=IF(AND(D8>0,D10>0), AVERAGE(D8,D10),"")

I think this does what you are looking for.

 

[99problems]

That is exactly it. I thought I had tried that, but I missed it by a command. Sometimes you can't see the forrest....but thank you for your help. Exactly what I needed.

 

[Joe4]

You are welcome.
Glad I was able to help.