Novice user trying to use variables including text and numbers

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
 
H

Héctor Miguel

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 __
 
R

Rachel Tulloch

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
 
S

Spiky

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top