Include cell in formula only if it DOES NOT ITSELF contain a formula

[puke]

Hello all. I'm brand new so please excuse any errors. I'm using 2007 for Windows. I have a range of cells containing measurements that I want to take the AVERAGE(...) difference of. However, I miss measurements sometimes, so some of those cells are actual verified measurements that I took, and some are AVERAGE(...)s of the cells to the left and right of them [for completeness' sake and to make my graphs pretty]. For instance:

	A​	B​	C​	D​	E​	F​
1	Measurement 1	181.2​	182.1​	182.2​	182​	182.5​
2	Measurement 2	183​	184​	=AVERAGE(C2,E2)​	183.8​	184.2​
3	Difference:	1.8​	1.9​	1.7​	1.8​	1.7​
4	Average Difference:	1.78​
5	(True Average, Excluding Formulas):	1.8​

Makes sense, right? Well, I would like to get an overall AVERAGE(...) for the difference of all my measurements, but only the truly calculated ones. So I want to exclude the cells which contain a formula (or rather, in this case, which are derived from cells with a formula).

Is this possible? I'm willing to go the VBA route, but would prefer not to (only because my macro-enabled workbooks always seem to crash or do funny stuff).

Thank you very much for your help.

 

[dreid1011]

Perhaps this:

=AVERAGE(IF(ISFORMULA(G3:G6),,G3:G6))

Change G3:G6 to suit your data.

 

[puke]

Perhaps this:

=AVERAGE(IF(ISFORMULA(G3:G6),,G3:G6))

Change G3:G6 to suit your data.

Thanks for the reply. That would work perfectly, but ISFORMULA(...) is not available in Excel 2007.

 

[Joe4]

Thanks for the reply. That would work perfectly, but ISFORMULA(...) is not available in Excel 2007.

Might need to use VBA then.
Is that an option?

(BTW, I REALLY recommend upgrading to 365. Some of the new functions are awesome!)

 

[dreid1011]

Thanks for the reply. That would work perfectly, but ISFORMULA(...) is not available in Excel 2007.

My apologies. I should brush up on what is available in each version.

 

[puke]

Might need to use VBA then.
Is that an option?

(BTW, I REALLY recommend upgrading to 365. Some of the new functions are awesome!)

I might have to go with VBA. Probably this solution implementation: Excel IsFormula Function Alternative Formula

Unless there's some trick I could do with conditional formatting or something? Maybe I'll do it manually lol

I purchased and installed 365 for a month because I needed to use a specific plug-in application. It literally almost bricked my Spectre x360 13 - the battery zapped all the way down and wouldn't charge; had to keep it plugged in. I don't want those always-online processes running either (yes, I know Windows 10 has plenty of its own already...)

 

[puke]

My apologies. I should brush up on what is available in each version.

It's my own fault for being a cheap luddite. 2007 is stable and does very nearly everything I want it to, though.

 

[Joe4]

I might have to go with VBA. Probably this solution implementation: Excel IsFormula Function Alternative Formula

Yes, that is one option I was thinking of. Or just loop through all the cells, calculate the averages, and hard-coded the missint values.

Unless there's some trick I could do with conditional formatting or something? Maybe I'll do it manually lol

Conditional Formatting cannot return values. It can only be used to change the formats of cells (color, bolding, etc).

 

[Joe4]

2007 is stable and does very nearly everything I want it to, though.

Well, I guess not quite everything!