Formula which works in one cell but not another?

[shiftyspina]

Hi all,

Im really confused over a formula i have in my spreadsheet, it works in most cells except those that have ".....Blue Whale - GBP" at the end of them, i highighted in yellow which ones dont seem to be working, would someone please mind taking a look for me, as i hae tried for the last 2 hours and still dont get why it doesnt work...

It would be saving me alot of anger and stress

Thanks

Dan

[Izandol]

Your formula does work - it returns 0. This is hidden by conditional formatting. The 0 is returned because the AVERAGEIFS formulas refer to cells in column T which have Text rather than number values.

[ragulduy]

It looks to me as though some of the data in column S is entered as text (the currency symbol is part of the cell value, not formatting) whereas others are a number value.

The blue whale ones happen to correspond to ones with the currency symbol "hard-coded", for example, if you look in the formula bar of S14 you wil see "£11.50" whereas the formula bar of S57 is "18.60"

I think this corresponds to whether the values are pounds or euros.

I think if you change your column T formula to:
=IF(K14="EUR",1*S14/$G$1,IF(K14="USD",1*S14/$G$2,1*S14))
in T14 and copy down

that might fix things.

[shiftyspina]

Izandol, yes that would be perfect if there was no value in column S, but for those parts there is, so im happy the iferror part is working, but couldnt figure out why it wouldnt pick up the value,

I will give that a try yudlugar thank you, hopefully thats the reason

[shiftyspina]

Perfect!! that fixed it thank you

[ragulduy]

No problem - For what it's worth I think Izandol was suggesting the same as me - you didn't have a value, you had text, so an arithmetic function such as averageif() would not work with your data.

[shiftyspina]

Yeah i appreciate both your responses, and quick as per usual, it was bugging me for hours why it didnt work (it did, but returned the 0 because of the formatting as mentioned by both of you) i had used conditional formatting to make the s column EUR, USD, or GBP, i must of missed some of the cells out and not extended the rule when i first set it, but using the formula you have given me doesnt need any formatting in column s now

[Jonmo1]

Many of your values in column S on the Virticle Tab are not really numbers.
Therefor your averageif is resulting in a #Div/0 error because there are no numbers to take an average from.
Which is then hidden by the IFERROR.

Use ISNUMBER to see
=ISNUMBER(S6) and fill down.

It appears to be related to the ones that end with GBP in column CH