divide 2 numbers based on a condition

[srinidhi]

I need to divide 2 numbers based on a condition


A1 has June, B3:M3 has months, June, July, AUG....


There are numbers in B4:M4 & B5:M5.


I need to divide B5/B4, if the month is June in A1, if it is July in A1, it should c5/c4 & so on

 

[Luke M]

Something like this?

=IF(B3=$A$1,B5/B4,"Not the right month")


EDIT: Ignore this, I misunderstood the question.

 

[NARAYANK991]

Hi Srinidhi ,


Try :


=OFFSET(B5:M5,0,MATCH(A1,B3:M3,0)-1)/OFFSET(B4:M4,0,MATCH(A1,B3:M3,0)-1)


Narayan


EDIT : Enter the above as an array formula ( using CTRL SHIFT ENTER ).


EDIT : Make it more general by using :


=OFFSET(B5:M5,0,MATCH(LEFT(A1,3)&"*",B3:M3,0)-1)/OFFSET(B4:M4,0,MATCH(LEFT(A1,3)&"*",B3:M3,0)-1)


Now , A1 can have Jan or January , Sep or Sept or September ; the same goes for the range B3:M3.

 

[Luke M]

Wow, I completely mis-read the original question. =(


I think a non-array version of Narayan's formula would be:

=OFFSET(B5,0,MATCH(A1,B3:M3,0)-1)/OFFSET(B4,0,MATCH(A1,B3:M3,0)-1)


Could also do this, which is shorter to write:

=SUMPRODUCT(B5:M5/B4:M4*(B3:M3=A1))