Cell average issues

[joramos10]

I'm try to create a formula that takes a percentage in four cells per page up to 6 pages. So with a possibility of 24 total entries. that I fill in weekly. Lets say I only have 18 entries that week. So I will like the formula to see that there is only 18 entries and give me the average percentage on those 18 entries not the total 24, the rest will be zero. In the current page the cells are position in (H18,B18,B5,H5) location.

Any info will be apperciated

[6StringJazzer]

Have you tried

=AVERAGE(H18,B18,B5,H5)

It will ignore blank cells.

[mehmetcik]

Please Login or Register  to view this content.

[joramos10]

I have tried this, but the percentage don't come out correctly. For example in a given week I have a possibility of 24 cells that could get a percentage. but on average weeks, I only populate a total of 18 on that formula will still count the other 6.
For example 18 cells will add to 840/18 = 49 but if those 24 are accounted for 840/24=35. Hopefully this breaks down what i'm trying to accomplish. But thank you for your input.

[joramos10]

Thanks for responding I will give this a try.

[joramos10]

Thanks for the formula, but it not work for me.

this is what i'm trying to work with.====> AVERAGEIF("B5, H5, B18, H18,M5,S5,M18,S18", ">1%","")

[6StringJazzer]

Try

AVERAGEIF((B5, H5, B18, H18,M5,S5,M18,S18), ">1%")

[joramos10]

Thank you for the help but it gave me this #value! error

[6StringJazzer]

My formula was incorrect. You cannot use a discontiguous range in an XXXIF type function so you have to build your own.

Please Login or Register  to view this content.

The "No Values" message comes up if there are no values >1% in any of the cells.

[joramos10]

Thank you so much for that formula, I'm running it through the motions to make sure it doesn't have no kinks.

=IFERROR((H18+B18+B5+H5+M5+M18+S5+S18+X18+X5+AD5+AD18+B38+H38+B51+H51+M38+S38+M51+S51+X38+AD38+X51+AD51) / ((H18>0.01%)+(B18>0.01%)+(B5>0.01%)+(H5>0.01%)+(M5>0.01%)+(M18>0.01%)+(S5>0.01%)+(S18>0.01%)+(X18>0.01%)+(X5>0.01%)+(AD5>0.01%)+(AD18>0.01%)+(B38>0.01%)+(H38>0.01%)+(B51>0.01%)+(H51>0.01%)+(M38>0.01%)+(S38>0.01%)+(M51>0.01%)+(S51>0.01%)+(X38>0.01%)+(AD38>0.01%)+(X51>0.01%)+(AD51>0.01%))*100,"No values")

As you can see there is a lot of cells, but at the moment the the formula is showing a +/- of .15 which its still ok for me.
But one last request, each percentage represents a truck. I was thinking to create a separate cell that when there is a value < 1% to show it a 1 unit, whith a possibility of 24 as you can see.

[joramos10]

Thank you so much for that formula, I'm running it through the motions to make sure it doesn't have no kinks.

=IFERROR((H18+B18+B5+H5+M5+M18+S5+S18+X18+X5+AD5+AD18+B38+H38+B51+H51+M38+S38+M51+S51+X38+AD38+X51+AD51) / ((H18>0.01%)+(B18>0.01%)+(B5>0.01%)+(H5>0.01%)+(M5>0.01%)+(M18>0.01%)+(S5>0.01%)+(S18>0.01%)+(X18>0.01%)+(X5>0.01%)+(AD5>0.01%)+(AD18>0.01%)+(B38>0.01%)+(H38>0.01%)+(B51>0.01%)+(H51>0.01%)+(M38>0.01%)+(S38>0.01%)+(M51>0.01%)+(S51>0.01%)+(X38>0.01%)+(AD38>0.01%)+(X51>0.01%)+(AD51>0.01%))*100,"No values")

As you can see there is a lot of cells, but at the moment the the formula is showing a +/- of .15 which its still ok for me.
But one last request, each percentage represents a truck. I was thinking to create a separate cell that when there is a value < 1% to show it a 1 unit, whith a possibility of 24 as you can see.

[joramos10]

Thank you so much for that formula, I'm running it through the motions to make sure it doesn't have no kinks.

=IFERROR((H18+B18+B5+H5+M5+M18+S5+S18+X18+X5+AD5+AD18+B38+H38+B51+H51+M38+S38+M51+S51+X38+AD38+X51+AD51) / ((H18>0.01%)+(B18>0.01%)+(B5>0.01%)+(H5>0.01%)+(M5>0.01%)+(M18>0.01%)+(S5>0.01%)+(S18>0.01%)+(X18>0.01%)+(X5>0.01%)+(AD5>0.01%)+(AD18>0.01%)+(B38>0.01%)+(H38>0.01%)+(B51>0.01%)+(H51>0.01%)+(M38>0.01%)+(S38>0.01%)+(M51>0.01%)+(S51>0.01%)+(X38>0.01%)+(AD38>0.01%)+(X51>0.01%)+(AD51>0.01%))*100,"No values")

As you can see there is a lot of cells, but at the moment the the formula is showing a +/- of .15 which its still ok for me.
But one last request, each percentage represents a truck. I was thinking to create a separate cell that when there is a value < 1% to show it a 1 unit, whith a possibility of 24 as you can see.