IF function

[alesbury]

Hi All,

I'm new to Excel really, but have looked through a number of the posts here to try to find a solution to my problem - no joy.

I have two set of monthly data which increase at different rates.

When Data A becomes higher than Data B i need to display the month this occurs in a new cell.

Any thoughts ?

Many thanks in advance.
S

 

[MrKowz]

Can you please provide an example of how your data is laid out, as well as your desired output? I'm not sure where the formula needs to look for the month, where Data A is, and where Data B is, etc...

 

[GlennUK]

How about an example layout? Or at least describe the layouts, and tell where the relevant date value is in relation to the data.

 

[T. Valko]

Hi All,

I'm new to Excel really, but have looked through a number of the posts here to try to find a solution to my problem - no joy.

I have two set of monthly data which increase at different rates.

When Data A becomes higher than Data B i need to display the month this occurs in a new cell.

Any thoughts ?

Many thanks in advance.
S

Maybe something like this...

Book1
	A	B	C
1	_	Data A	Data B
2	1/1/2011	1	1
3	2/1/2011	2	5
4	3/1/2011	5	6
5	4/1/2011	8	9
6	5/1/2011	15	10
7	6/1/2011	19	20
8	7/1/2011	26	25
9	8/1/2011	27	26
10	9/1/2011	35	33
11	10/1/2011	50	55
12	11/1/2011	60	66
13	12/1/2011	92	77
Sheet1

This array formula**:

=INDEX(A2:A13,MATCH(TRUE,B2:B13>C2:C13,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

 

[alesbury]

Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
DATA A £1,044.61 £1,289.22 £1,533.82 £1,778.43 £3,823.04 £4,067.65
DATA B £3,314.17 £3,427.74 £3,541.30 £3,654.87 £3,768.44 £3,882.01

I'm not sure how to get a table imported , however i think you get the idea..

The month headings should line up with the Data set below them.

However. The two data sets are a result of other calculations thus when the input data is changed the Month that Data B becomes smaller than Data A will change and that is what i need to show in a new cell.

Clear as mud ?

I can supply more if required. thanks again for your help.
S

 

[T. Valko]

Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
DATA A £1,044.61 £1,289.22 £1,533.82 £1,778.43 £3,823.04 £4,067.65
DATA B £3,314.17 £3,427.74 £3,541.30 £3,654.87 £3,768.44 £3,882.01

I'm not sure how to get a table imported , however i think you get the idea..

The month headings should line up with the Data set below them.

However. The two data sets are a result of other calculations thus when the input data is changed the Month that Data B becomes smaller than Data A will change and that is what i need to show in a new cell.

Clear as mud ?

I can supply more if required. thanks again for your help.
S

Book1
	A	B	C	D	E	F	G
1	_	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
2	Data A	1,044.61	1,289.22	1,533.82	1,778.43	3,823.04	4,067.65
3	Data B	3,314.17	3,427.74	3,541.30	3,654.87	3,768.44	3,882.01
Sheet1

This array formula**:

=INDEX(B1:G1,MATCH(TRUE,B2:G2>B3:G3,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[alesbury]

LEGENDS !! thanks guys this worked a treat !

 

[T. Valko]

LEGENDS !! thanks guys this worked a treat !

You're welcome. Thanks for the feedback!

 

[alesbury]

there's a little bonus question if you fancy it that i've just posted !!