finding average of two different columns

[mike rand]

hello all

I have two columns d9 to d45 and h9 to h45

all cells contain a percentage from 0 to 100

in cell d1 i need to add all the percentages in all cells d9to d45 and h9 to h45 and divide by the number of percentages

ive tried different formulas but keep getting errors because some of the cells contain blanks

what formula would i use to calculate the average of all the percentages without causeing error because some cells in columns do not contain information


thanks
mike rand

[XOR LX]

Hi.

Not sure I understand. AVERAGE ignores any blank cells within the range, so you can use simply:

=AVERAGE(D9:D45,H9:H45)

As a side, unless each of those percentages is derived from the same base value (i.e. all are some number divided by, for example, 20), then taking an average of percentages is, mathematically speaking, a very misleading statistic.

For example, if cell D9 was the result of 10/10, i.e. 100%, and cell H9 was the result of 1/1000, i.e. 0.1%, then taking the average of D9 and H9 would give 50.05%. However, 10/10 and 1/1000 are, in total, just 11/1010. As such, the "proper" combined measure for these two is only 1.089%.

Regards

[mike rand]

yes this is a formula i have tried already and it gives me a #DIV/0 ERROR in cell D1

all percentages are out of 100

example
d9 is 80%
d10 is 85%
d11 is 82%
d12 is blank
d13 is blank
d14 is 83%

and so on up to d45

h9 is blank
h10 is 80%
h11 is 85%
h12 is 90%
and so on up to h45

i need to add all the percents from d9 to d45 and h9 to h45 and divide by the number of entries excluding the blank cells untill they are inputted with a percentage.

hope its clearer
thanks
mike rand

[XOR LX]

Are your entries in those columns actual percentages, or text entries which just look like percentages?

For example, what does the formula:

=ISNUMBER(D9)

return?

If the value in D9 is a proper, Excel-recognised percentage, the above will return TRUE. If not, can I ask how/why you have a sheet with text percentages, rather than real percentages? Is this data imported from some other source?

Regards

[mike rand]

all cells are formatted as percentages

when i put
=isnumber(d9) in cell d1 it returns TRUE in cell

i inputed 80% in all cells a total of 32 entries and used
=AVERAGE(D9:D45,H9:H45)
and get answer 0.8 in cell d1
but even if i remove the percentages the 0.08 still remains in the cell d1 even with only one entry for some reason
when i remove the last entry the 0.8 disappears and a #div/0 error appears

thanks again for your help
mike rand

[XOR LX]

but even if i remove the percentages the 0.08 still remains in the cell d1 even with only one entry for some reason

??

But of course! The average of 80% is 80%!

when i remove the last entry the 0.8 disappears and a #div/0 error appears

??

Again, of course! You cannot have an average of nothing!

Regards

[mike rand]

lol dah im stupid lol

ok so if no entries with the formula u gave how do i get cell d1 to show blank instead of error message until percentages are inputed

i feel so stupid not to see that lol ive formated d1 to be a percent as well is there a simple reason why it shows 0.8 and not 80%

thanks

mike

[XOR LX]

No worries!

=IF(COUNT(D9:D45,H9:H45),AVERAGE(D9:D45,H9:H45),"")

Cheers

[mike rand]

awesome worked perfect

last thing on this thread is the answer in d1 is showing as a decimal not matter how i format the cell i cant get it to show as a % instead of decimal

thanks again
mike

[XOR LX]

You mean Format Cells/Number/Percentage didn't work?

Regards

[mike rand]

You mean Format Cells/Number/Percentage didn't work?

Regards

No. It did work but it is showing the percentage as a decimal 0.8. Instead of 80%. As id like it.
I tried formatting the cell to show percentages but its showing as decimal
Thanks mike

[XOR LX]

I'm sorry but I don't understand. You mean it shows 0.8 or 0.8%?

Regards

[mike rand]

it shows 0.8 but ive formated the cell as a percentage with % but it shows up as a decimal 0.8 when i input 80% in all the cells required the average is 80% but shows up as decimal instead of a % the formula in the cell is =IF(COUNT(D9:D45,H9:H45),AVERAGE(D9:D45,H9:H45),"") and ive imputed 80 % in each of the cells which should give me an 80% average but its showing up as 0.08

thanks
mike

[mike rand]

heres a copy of the wsheet the formula in question is in d1

[XOR LX]

You need to change the format in your Conditional Formatting rules to Percentage as well.

By the way, re those rules, I think you might need to change them to <0.8, etc., rather than <80, since 80% is 0.8, not 80. Also, you seem to have the same rule repeated many times?

Regards

[mike rand]

thanks
mike rand

[mike rand]

awesome awesome finally got it thank u so much youve been so patient with my problem works perfect now
thanks again
mike rand

[XOR LX]

No worries.

Cheers!