average of non-numeric cells without helper column

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
328
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Maybe
=IF(COUNTIFS($A$2:$A$8,A2,$C$2:$C$8,"<>"&C2)>0,"check","ok")
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top