Testing for a consistent value counting blanks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mwheelock@ups.com - 14 Feb 2007 19:49 GMT

I am trying make a formula that returns a value if all of them are the
same in a column but it has to include blanks. Say column H1:H10 is
the range and one and H1=2, H4=""or blank, H5=2 and so on....I need 2
to populate the cell. Now lets say H1=2, H4=""or blank, H5=4, I need
it to return an error message or a not available because H5 is a
diferent value than H1. I am trying to protect against a key entry
error in a spread sheet. Not all of the cells in the column will be
populated(some will remain blank) but the ones that are must be the
same value in the column. Any suggestions?

Reply to this Message

Bernie Deitrick - 14 Feb 2007 21:52 GMT

M,

Select H1:H10, use Data / Validation Custom, with the formula

=MAX(H$1:H$10)=MIN(H$1:H$10)

HTH,
Bernie
MS Excel MVP

>I am trying make a formula that returns a value if all of them are the
> same in a column but it has to include blanks. Say column H1:H10 is
[quoted text clipped - 5 lines]
> populated(some will remain blank) but the ones that are must be the
> same value in the column. Any suggestions?

Reply to this Message