Formula based on cell value

[Mac88]

First of all greetings to everyone. This is my first post, but i am following this forum for a long time and i found a lot of answers here

But now i have a specific question where i didn't find an answer here, so please help if you know how to make it work.

I have a large table with a lot of values where i have to compare the values from each month. I have written all the formulas that i need and everything i working ok.. Now i have all the formula fixed based on column number(column A,b,c,d...), but the problem is that the months won't be always in the same columns.

So i would need a formula like this: Where the call value matches "Mar" execute some sample formula, where the call value matches "Jun" execute another formula and so on....

	Jan	Feb	Mar	Apr	Maj	Jun	Jul	Avg	Sep	Okt	Nov	Dec
test	1	2	-3	4	5	6	7	8	-5	10	11	12
test1	1	2	3	4	5	6	7	8	9	10	11	12
test2	1	2	3	4	-5	6	7	8	9	10	11	12
test3	1	2	3	4	5	6	7	8	9	10	11	12
test4	1	2	3	4	5	6	7	8	9	10	11	12
test5	1	2	3	4	5	6	7	8	9	10	11	12
test6	1	2	3	4	5	6	7	8	9	10	11	12
test7	1	2	3	4	5	6	7	8	9	10	11	12
test8	1	2	3	4	5	6	7	8	9	10	11	12
test9	1	2	3	4	5	6	7	8	9	10	11	12
test10	1	2	3	4	5	6	7	8	9	10	11	12
test11	1	2	3	4	5	6	-7	8	9	10	11	12
test12	1	2	3	4	5	6	7	8	9	10	11	12
test13	1	2	3	4	5	6	7	8	9	10	11	12

<tbody>
</tbody>

Hope i wrote it clearly enough...

Thanks for any help.

 

[nardagus]

To be honest I don't know what your formulas look like but why don't you use combination of MATCH, ADDRESS, INDIRECT and ROW functions? Something like this:

Excel 2007

	A	B	C	D
1		Feb	Jan	Result
2	test	123	444	444
3	test1	342	555	555

<tbody>
</tbody>

Arkusz1​

Worksheet Formulas Cell Formula D2 =INDIRECT(ADDRESS(ROW(),MATCH("Jan",$1:$1,0),1,,"sheet1")) D3 =INDIRECT(ADDRESS(ROW(),MATCH("Jan",$1:$1,0),1,,"sheet1")) <tbody> </tbody>

<tbody>
</tbody>

Function MATCH provides column number where January resides to ADDRESS formula. ROW returns row number also for ADDRESS.
ADDRESS formula returns cell address as a text which is translated as a range by INDIRECT function. This way it returned a proper value. You can use this formula combination with other formulas which require cell address.

 

[Mac88]

Hi Nardagus,

I'm using just a nested IF formula. like bellow. The formula is in column I.

And now i have the formula fixed based on column (C,E,G) but the months won't be always in the same column.
So i would need that the formula would check the names of the month and check if the value is there negative, if not perform the next check.

For example: Check if the value under month Mar is negative, if yes give result Q1 if not perfom the same check for month Jun, and so on....

"=IF(C:C<0;"Q1";IF(E:E<0;"Q2";IF(G:G<0;"Q3";"")))"

A	B	C	D	E	F	G	H	I
Jan	Feb	Mar	Apr	Mai	Jun	Jul		result
1	2	-3	-5	-9	-10	-13		Q1
2	5	2	1	-10	-12	-13		Q2
3	2	6	1	4	5	-15		Q3

<tbody>
</tbody>

 

[nardagus]

Let me get this straight.

Do you want to check which month you got first negative value in a particular row? Then based on a month return which quarter it was?
Then I suggest to do a translation table for months vs quarters in another worksheet:
Excel 2007

	A	B
1	Month	Quarter
2	Jan	Q1
3	Feb	Q1
4	Mar	Q1
5	Apr	Q2
6	May	Q2
7	Jun	Q2
8	Jul	Q3
9	Aug	Q3
10	Sep	Q3
11	Oct	Q4
12	Nov	Q4
13	Dec	Q4

<tbody>
</tbody>

Month vs Quarters​

Then based on your example a formula would look like this:

Excel 2007

	A	B	C	D	E	F	G	H	I
1	Jan	Feb	Mar	Apr	May	Jun	Jul
2	1	2	-3	-5	-9	-10	-13		Q1
3	2	5	2	1	-10	-12	-13		Q2
4	3	2	6	1	4	5	-15		Q3

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula I2 {=INDEX('Month vs Quarters'!B:B,MATCH(INDIRECT(ADDRESS(1,MATCH(1,IF(A2:G2<0,1),0),1,1)),'Month vs Quarters'!A:A,0))} I3 {=INDEX('Month vs Quarters'!B:B,MATCH(INDIRECT(ADDRESS(1,MATCH(1,IF(A3:G3<0,1),0),1,1)),'Month vs Quarters'!A:A,0))} I4 {=INDEX('Month vs Quarters'!B:B,MATCH(INDIRECT(ADDRESS(1,MATCH(1,IF(A4:G4<0,1),0),1,1)),'Month vs Quarters'!A:A,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

What I did was to create an array formula. So:
Lets analyze second row.
IF formula (thanks to array formula) checks every cell in a range for negative value. If yes then it returns 1 which is looked for by MATCH formula. When MATCH finds 1 then it returns actual position of a cell with negative value in the range. And since I've used a range starting from a column A it returns actual column. ADDRESS and INDIRECT allow me to get month name from a first row and a column where was negative value found (column 3). Then using second MATCH with a combination with INDEX I get info about a quarter from a second sheet.

I hope this is what you needed and my explanation is clear.