AVERAGE With TEXT Formula Problem

[John Caines]

Hello all.
I have a formula which is working for a cell which is;

=IF(LEN($H$24)=0,"",AVERAGE($H$24:$H$274))

This is ok,, but say it gives me the answer 24.94

What I'm looking to do is in the same cell show some text before this,, so it says;
Avg:24.94

I've tried;

=IF(LEN($J$24)=0,"","Avg:" ,(AVERAGE($H$24:$H$274)))

This didn't work,,, but can't be too far off.
Can anybody show me please what is wrong?
I just can't quite get it,,, I know its very close,, (I think )

Many Thanks
John Caines

 

[VoG]

Hi John. Try

=IF(LEN($H$24)=0,"","Avg: "&AVERAGE($H$24:$H$274))

 

[John Caines]

Hi VOG,,
Many thanks for your reply,,
Stange,,,
the answer it gives now is;
Avg: 24.9411764705882

I came across this the other day,,,
I think I have to go into excel options and save the sheet somehow so it doesn't display these long number strings,,,,
Funny why it's done that? Not sure.

Just tried your formula in another cell with the SUM function instead of AVERAGE,, working fine!

Again,, many thanks for this,,, only the & missing,,, so I was almost there hehe,,
Thanks VOG
Best Regards
John Caines

 

[VoG]

If you want to display to two decimal places try

=IF(LEN($H$24)=0,"","Avg: "&ROUND(AVERAGE($H$24:$H$274),2))

 

[John Caines]

Many thanks again VOG!!
What I just replied,,, I was trying in the excel options,,thinking it was something to do with;
Tools > Options > Calculation
then select "Precision as displayed"

But obviously not!!!
many thanks again,,,
The spreadsheet is looking good.

Thanks for your help VOG,,
Very much appreciated.
All the best
John Caines