Dragging down a Indirect formula.

[Chocolate_bar]

Hello, being a novel excel user I usually find my answers on this and other forums. This problem has stumped me I have tryed to write the formula many diffrent ways. All do not work as well as this one. I need to be able to have excel update my cell refrences when I drag this formula down. Any help will be appreciated, and I'm sorry if I have written this crudely. =SUM(SUMIF(INDIRECT({"AC12","AE12","AG12","AI12"}),">0")/4)

 

[Aladin Akyurek]

Without me trying to explain my spread sheet the desired result from your table given is 2%

If you are displeased with the samples I posted, try to provide yourself one that is representative of your spreadsheet along with the desired result.

By the way, I'm not going to ask how you arrive at 2% given the sample I provided and if the formula you have in post #5 is correct in all cases, there should be no need for any sample at all.

 

[Chocolate_bar]

Does this make more sense?

ab	ac	ad	ae	af	ag	ah	ai	aj
8/8	100%	3/4	75%	1/2	50%	8/8	100%	81%

<colgroup><col style="width: 104pt; mso-width-source: userset; mso-width-alt: 5083;" width="139"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 88pt;" width="117"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" width="84"> <col style="width: 88pt;" span="4" width="117"> <col style="width: 116pt; mso-width-source: userset; mso-width-alt: 5668;" width="155"> <tbody>
</tbody>

 

[gaz_chops]

Without me trying to explain my spread sheet the desired result from your table given is 2%, actually both examples are 2%. The formula is based on other formulas in those columns based on percentages if that makes sense. the ">0" is to sum cells that have something in them.

If you just want to sum the cells that have something in them, you don't need to test if >0, if they are empty they will be effectively 0!
so simply use
=SUM(AC12,AE12,AG12,AI12)/4

 

[Chocolate_bar]

Sorry Aladin its not that Im displeased I'm very new thank you for being patient!

 

[Chocolate_bar]

Yes gaz_chops, but the cells hold formulas and at 100% it returns 25% using above formula.

 

[gaz_chops]

Then
=SUM(AC12,AE12,AG12,AI12)/4
does exactly the same as your formula in post #5, you don't need to test if >0.

 

[Chocolate_bar]

---

Your right sorry, and I had to make things complicated. I thought the formula I had worked, but it seems I overlooked something the"/4" The 25% should =100%

2	100%							25%
	0%	19	112%	9	86%	9	86%	71%

<tbody>
</tbody>

 

[Aladin Akyurek]

---

Your right sorry, and I had to make things complicated. I thought the formula I had worked, but it seems I overlooked something the"/4" The 25% should =100%

2	100%							25%
	0%	19	112%	9	86%	9	86%	71%

<tbody>
</tbody>

How do you obtain 100% with this sample? That is, how do hand-calculate that?

 

[gaz_chops]

---

Your right sorry, and I had to make things complicated. I thought the formula I had worked, but it seems I overlooked something the"/4" The 25% should =100%

2	100%							25%
	0%	19	112%	9	86%	9	86%	71%

<tbody>
</tbody>

I can see that the 71% is obtained from the previous formula, but how is the 25% calculated? And why should it be 100%?

 

[Chocolate_bar]

Beacuse no other data would be entered therefore the total percentage of that would be divided by (4) because I have 4 columns that I'm taking a total of % out of. So i would need that formula to equal 100%. Sorry If its not clear I dont really know how to explain myself that well. Thanks for the help!