Novice user trying to use variables including text and numbers

  • Thread starter Thread starter Rachel Tulloch
  • Start date Start date
R

Rachel Tulloch

I'm completely self taught so hope this is possible

I've tried a number of formula but the nearest is

=IF(($D$7<2)*($F$7>100)*($D$7="NA"),0,1)

what i need to do is assign a value of 1 or 0 when adding differen
arguments

I need a result of 0 if D1 is under 2.00 F1 is over 100 or the resul
in the D coloum reads NA, can't change NA to read something else as it
imported from another source.

I also need a result of 1 if D1 is 2.00 or over, if F1 is under 100
everyway i try to do this counts NA as being larger than 2.00

Any help gratefully received, thank
 
hi, Rachel !

not sure i'm following you correctly (but) you might want to give a try...

=--if(d7<>"na",and(d7>=2,f7<100))

hth,
hector.

__ OP __
 
Hi Hector, Thanks for your help, thought that had almost worked but i
didn't.

Animal, Error Rate, Count

Dogs, 0.20, 38, *
Cats, 7.00, 120, *
Birds, NA, 0, *
Frogs, NA, 150,
Hamster, 1, 500,
Basically I need to Find total of error rates over 2.00 an
counts under 100 but i need NA to equal 0 and as its imported fro
elsewhere text needs to be NA. I have put a star at coloums tha
should count as 1 as if a row fits errors over 2.00 and count unde
100, it should still count as 1 and not 2
 
There are many ways to write this that should all work. Here's one:
=IF(D1="NA",0,IF(AND(D1>=2,F1<100),1,0))

But, in your example you say those starred should all be a 1. But you
said 1 should be returned if the rate is over 2.00 AND count is under
100. (did you mean OR?)And you said anything with an NA should return
0. By my reading, all of your examples should return 0.

Also, in your 2nd post you suddenly mentioned returning a 2. Is that a
typo?

So, what is wrong in your posts? Need to get that fixed first.
 
Back
Top