Multiple if/then, OR formulas

A

Ashley

Hi! Any help is greatly appreciated.

I have a multi-row, multi-column worksheet. In column J I have a list of
numbers. In column O I have a different set of numbers. For each row, there
is a unique value in column J and column O.

1) For each row, in column P, I would like the numeric amount from column N
to appear IF 1) the number in column J equals 07, 08, 52, 60, 70, 71, or 80
OR 2) if the number in column O equals 14, if neither of these are true, then
I would like a 0 to appear.

Any ideas or suggestions? Thanks!
 
D

Dave F

This should do it: =IF(OR(J1=7,J1=8,J1=52,J1=60,J1=70,J1=71,J1=80,O1=14),N1,0)

Enter in P1 and fill down.

Dave
 
A

Ashley

Dave, It's working for the values in J, but it's not working if there's a 14
in column O. And ideas?

Thanks, Ashley
 
D

Dave F

I just tested it out on my machine and it works if O1 = 14.

Are your values in column O formatted as numbers?

Dave
 
D

David Biddulph

Ashley said:
Hi! Any help is greatly appreciated.

I have a multi-row, multi-column worksheet. In column J I have a list of
numbers. In column O I have a different set of numbers. For each row,
there
is a unique value in column J and column O.

1) For each row, in column P, I would like the numeric amount from column
N
to appear IF 1) the number in column J equals 07, 08, 52, 60, 70, 71, or
80
OR 2) if the number in column O equals 14, if neither of these are true,
then
I would like a 0 to appear.
Click to expand...

=IF(OR(J1=7,J1=8,J1=52,J1=60,J1=70,J1=71,J1=80,O1=14),N1,0)
 
P

PCLIVE

If I understand you correctly, you need both conditions to be true. If so,
then you need to add an AND to the formula.
Try this:

=IF(AND(OR(J1=7,J1=8,J1=52,J1=60,J1=70,J1=71,J1=80,O1=14),O1=14),N1,0)

Regards,
Paul
 
A

Ashley

Dave, That's what I just checked. They were a download from a financial
system, and so they weren't. Now, a probably dumb question. Is there a
quick way to make the change other than retyping the numbers all the way
down? I've got about 26 worksheets to perform this calculation on. I tried
highlighted and doing a number format, but that didn't work.

Thanks again!
 
D

Don Guillett

another idea
=IF(SUMPRODUCT((j5={1,2,3})*1),n5,0)
to also use the OR
=IF(OR(o5=14,SUMPRODUCT((j5={1,2,3})*1)),N4,0)
 
D

Dave F

The easiest way to convert text to number is do the following:

1) Enter 1 in an empty cell
2) Copy that cell
3) Select the column of text you want changed to numbers
4) Paste--Special--Multiply
5) Your text should now be numbers.

BTW, what financial system did the numbers come from? Some systems, like
SAP, include extraneous characters that you may need to get rid of by running
the CLEAN function. But try what I suggest above first, then see where you
are.

Dave
 
A

Ashley

Dave, That worked perfectly! Thanks.

Our financial system is a homegrown system for our University System; we use
Cognos to download data from it, and usually, our numbers come formatted as
text.
 
Top