Difficulty displaying text

T

Tanya

I have this formula which works wonderfully: [formatted as a percentage]

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01

however, I now need to average the column this formula is in, which is fine
and ordinarily this wouldn't be a problem, however I wish the cell this
formula is placed to show as "" [text] if there are no values in P20:AD20.
My need to do this is that there could more than likely be rows where no
entries have been made, thereby showing 0% hence by changing the outcome to
text when no data has been entered in relative cells will allow me to get an
accurate average.

I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
of the formula itself.

Any help would be appreciated, I cannot take credit for the above formula,
'Toppers' from this support group came up with it which I am extremely
grateful for.

Thanks
Tanya
 
M

Max

.. I wish the cell this formula is placed to show as "" [text]
if there are no values in P20:AD20.

If P20:AD20 are expected to house only numbers,
think you could try a front IF, something like this:
=IF(COUNT(P20:AD20)=0,"",<your formula>)

Tanya said:
I have this formula which works wonderfully: [formatted as a percentage]

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01

however, I now need to average the column this formula is in, which is fine
and ordinarily this wouldn't be a problem, however I wish the cell this
formula is placed to show as "" [text] if there are no values in P20:AD20.
My need to do this is that there could more than likely be rows where no
entries have been made, thereby showing 0% hence by changing the outcome to
text when no data has been entered in relative cells will allow me to get an
accurate average.

I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
of the formula itself.

Any help would be appreciated, I cannot take credit for the above formula,
'Toppers' from this support group came up with it which I am extremely
grateful for.

Thanks
Tanya
 
T

Tanya

Thank you Max, I appreciate your help.
You saved me hours of work. I had pondering heaps of different IF statements
and you got it straight away.
Regards
Tanya

Max said:
.. I wish the cell this formula is placed to show as "" [text]
if there are no values in P20:AD20.

If P20:AD20 are expected to house only numbers,
think you could try a front IF, something like this:
=IF(COUNT(P20:AD20)=0,"",<your formula>)

Tanya said:
I have this formula which works wonderfully: [formatted as a percentage]

=SUMPRODUCT(--(MOD(COLUMN($O$3:$AC$3),2)=1),($O$3:$AC$3),--(MOD(COLUMN(P20:AD20),2)=0),(P20:AD20))*0.01

however, I now need to average the column this formula is in, which is fine
and ordinarily this wouldn't be a problem, however I wish the cell this
formula is placed to show as "" [text] if there are no values in P20:AD20.
My need to do this is that there could more than likely be rows where no
entries have been made, thereby showing 0% hence by changing the outcome to
text when no data has been entered in relative cells will allow me to get an
accurate average.

I have considered ISNUMBER and ISBLANK but as a loss due to the complexity
of the formula itself.

Any help would be appreciated, I cannot take credit for the above formula,
'Toppers' from this support group came up with it which I am extremely
grateful for.

Thanks
Tanya
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top