Another EXCEL formula problem

[John C. Harris, MPA]

I will try to explain this as best as I can.

I am using a form, that in two columsn uses the formula : =IF(F7>0,F7/E7,"No
Entry")
Where if the numbers in F7 and E7 have not been entered yet it gives the
result of "No Entry" in the field.

The problem arises in the last column where I am using this formula:
=IF(AND(G7>=0.75,I7>=0.8),1, 0)

Which tells me if the G7 and I7 are over the stated percentages it assigns a
"1", if not it assigns a "2". This all works ok until you get to the point
in the form where it has no entries and assigns the text "No Entry". The
formula in the last column returns a "1", which throws off the average
formula for that column.

How can I change this formula:
=IF(AND(G7>=0.75,I7>=0.8),1, 0)
to assign nothing if there is TEXT in the fields G7 and I7?

Hope I explained that good enough.

TIA

 

[JohnI in Brisbane]

John,

try either of these formulas-

=IF(OR(ISTEXT(G7),ISTEXT(I7)),"",IF(AND(G7>=0.75,I7>=0.8),1, 0))

=IF(AND(ISNUMBER(G7),ISNUMBER(I7)),IF(AND(G7>=0.75,I7>=0.8),1, 0),"")


They both do the same thing.


regards,

JohnI

 

[John C. Harris, MPA]

Thank a lot. I understand the language you used. Would have taken me forever
to figure that out. I appreciate it.

 

[Tom Ogilvy]

=IF(count(G7,I7)<2,"",IF(AND(G7>=0.75,I7>=0.8),1, 0))

is a little simpler.

You showed in your original 1,0 and said it returns 1 or 2. Maybe this
is a typo.