Complex formula assistance please.
 

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

Complex formula assistance please.
 

On Tue, 14 Jul 2009 23:42:01 +0100, Colin Hayes
wrote:


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

Complex formula assistance please.
 

In article , Lars-Åke
Aspelin writes
On Tue, 14 Jul 2009 23:42:01 +0100, Colin Hayes
wrote:


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

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

Complex formula assistance please.
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Colin Hayes" wrote:

In article , Lars-Ã…ke
Aspelin writes
On Tue, 14 Jul 2009 23:42:01 +0100, Colin Hayes
wrote:


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

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

Complex formula assistance please.
 

In article , Shane
Devenshire writes
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