Excel help please - simple IF & AND functions...

DoubleCheese · 9 Jan 2013 at 13:32

I can't get my head round why this wont work for me.

Anyway, I have a Spreadsheet with some simple financial data on. Column J (Charges) will either have £0, £5, £12 or £17 (5+12). The spreadsheet is a database dump and I am trying to split the Charges into two other columns, N and O as follows:

-Column N
If J = £0 or £5 input £0 or £5
If J = £12 input £0
If J = £17 input £5

- Column O = J-N

The following works unless J = £12 where it returns £5 but it should be £0.
=IF(J2>=5,5,0)

I think the answer might be to have an AND function in there too but I can't get it to work.

Any ideas?

AMMUT · 9 Jan 2013 at 13:58

Try this:

Code:

=IF(OR(J2=0,J2=12),0,5)

Daveangle · 9 Jan 2013 at 14:05

Not sure I am entirely getting what you want;

If the value in J = £0 then 0
If the value in J = £5 then 5
If the value in J = £12 then 0
If the value in J = £17 then 5

?

If that is the case then you could do the following:

=IF(J2>=5,IF(J2=12,0,5),0)

That would probably work, but as I say I am not entirely sure what you want.

DoubleCheese · 9 Jan 2013 at 14:05

AMMUT said:
Try this:

Code:

=IF(OR(J2=0,J2=12),0,5)

This works perfectly! Thank you VERY much!

AMMUT · 9 Jan 2013 at 14:08

No problem. Should be easy to modify as well if you encounter other values later on.