average of non-numeric cells without helper column

[Newport Count]

Hi there,

I am trying to identify a mismatch based on non-numeric data in a spreadsheet but unable to do this without a helper column that converts it to a numerical value.

It seems like there should be an easier way to achieve this (without the helper column) but I gave up searching for it!

Any ideas?


Current data as follows:

Order Item Plant h f
123 10 G013 13 check
123 20 G120 120 check
456 10 G013 13 ok
456 20 G013 13 ok
789 10 G013 13 check
789 20 G013 13 check
789 30 G120 120 check


I would like the formula to highlight if an order number has more than one Plant per (line) Item, if so state 'check' otherwise 'ok'.


My current formulae are as follows:

h helper column: =IF(C2="G013",13,IF(C2="G120",120,"error"))...i did try =RIGHT(C2,3) but that didn't work.
f formula column: =IF(AVERAGEIF(A:A,A2,D:D)=D2,"ok","check")


Many thanks,
Rich

 

[Finalfight40]

When using RIGHT OR LEFT or something of that nature it will always convert it into text. To get around this you can use =RIGHT(C2, 3)+0 to convert it to a number.

 

[Newport Count]

Aha! I remember that one....thanks for that, this makes the helper column formula much easier to control but the is there a way to lose the helper column completely?

Can a formula recognise non-identical text values...I guess that using an AVERAGE formula is causing the issue as this must relate to numerical figures.


Many thanks,
Rich

 

[Finalfight40]

Have to be honest, im not 100% sure on what you are after but maybe this:

=IF(COUNTIF(A:A, A2) = COUNTIFS(A:A, A2, C:C, C2), "OK", "CHECK")

this also implies that G does not matter since it looks like it appears at the front of all your plants.

 

[Finalfight40]

Have to be honest, im not 100% sure on what you are after but maybe this:

=IF(COUNTIF(A:A, A2) = COUNTIFS(A:A, A2, C:C, C2), "OK", "CHECK")

this also implies that G does not matter since it looks like it appears at the front of all your plants.

When i said G does not matter, that was the worst explanation i could give so ignore that part.

 

[Fluff]

Maybe
=IF(COUNTIFS($A$2:$A$8,A2,$C$2:$C$8,"<>"&C2)>0,"check","ok")

 

[Newport Count]

Thanks a lot, both solutions work and no need for the helper column

 

[Fluff]

Glad we could help & thanks for the feedback