Complex formula assistance please.

[Colin Hayes]

HI

In my helper column in K2 , I'm trying to construct a formula which
combines and satisfies these arguments:

If D2 is larger than 25 and J2 equals 1 or 11 , then put 5 , otherwise
put J2.
If D2 is larger than 25 and J2 equals 2 , then put 6 , otherwise put J2.


I've been struggling with syntax. Can anyone advise?


Grateful for any help.



Best Wishes

 

[Lars-Åke Aspelin]

HI

In my helper column in K2 , I'm trying to construct a formula which
combines and satisfies these arguments:

If D2 is larger than 25 and J2 equals 1 or 11 , then put 5 , otherwise
put J2.
If D2 is larger than 25 and J2 equals 2 , then put 6 , otherwise put J2.


I've been struggling with syntax. Can anyone advise?


Grateful for any help.



Best Wishes

Try this formula:

=IF(D2<=25,J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5,J2)))

Hope this helps / Lars-Åke

 

[Colin Hayes]

Try this formula:

=IF(D2<=25,J2,IF(J2=2,6,IF(OR(J2=1,J2=11),5,J2)))

Hope this helps / Lars-Åke

Hi Lars-Åke

OK thanks for getting back.

It didn't work at first , but I think I figured out why.

For some reason , the numbers I have in J2 all have a ' in front of
them. This stops the formula working.

When I remove the ' , then it's fine. Is there a way to fix this in the
formula , or eradicate these ' in front of all my numbers?



Best Wishes

 

[Shane Devenshire]

I would get rid of the apostrophies, but if you must leave them then modify
the formula to read

=--IF(D2<=25,J2,IF(--J2=2,6,IF(OR(--J2=1,--J2=11),5,J2)))

or

=IF(D2<=25,VALUE(J2),IF(VALUE(J2)=2,6,IF(OR(VALUE(J2)=1,VALUE(J2)=11),5,VALUE(J2))))

To remove the apostrophies from in from of a range of cells choose a blank
cell and click Copy. Then select all the text entires (the cells with
apostrophies) and choose Edit, Paste Special, Add.

 

[Colin Hayes]

I would get rid of the apostrophies, but if you must leave them then modify
the formula to read

=--IF(D2<=25,J2,IF(--J2=2,6,IF(OR(--J2=1,--J2=11),5,J2)))

or

=IF(D2<=25,VALUE(J2),IF(VALUE(J2)=2,6,IF(OR(VALUE(J2)=1,VALUE(J2)=11),5,
VALUE(J2))))

To remove the apostrophies from in from of a range of cells choose a blank
cell and click Copy. Then select all the text entires (the cells with
apostrophies) and choose Edit, Paste Special, Add.

Hi Shane and Lars-Ake

OK thanks for that - its working perfectly now.

After some trial and error , I actually got around it in a more
long-winded way , by multiplying by 1 and copying down. I can see that
your solution is neater , but got there in the end.

Thanks for your time and expertise , I'm grateful.


Best Wishes