Multiple If Formulas

[clueless48]

Hi

I am trying to figure out a formula to work out the following.
To find the correct multiple to use for, when an area is entered into B9 which gives the row required and for the column required this is the numbers of weeks shown in B10 is equal to or less than the weeks shown in row 2.
Basically, what is the formula required if area 2 is selected in B9 and the number of weeks is 120 in B10 to return a value 0.33

 

[jkpieterse]

Please revisit your post. There are two "Area 3" rows and your example where area 2 and 120 weeks gives 0.33 seems wrong to me?

 

[Peter_SSs]

@clueless48

It would also be helpful & likely get you faster/better responses if you ..

1. Updated your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. Investigated XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be & to be able to easily copy your sample data for testing.
   (If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[clueless48]

Hi

I am trying to figure out a formula to work out the following.
To find the correct multiple to use for, when an area is entered into B9 which gives the row required and for the column required this is the numbers of weeks shown in B10 is equal to or less than the weeks shown in row 2.
Basically, what is the formula required if area 2 is selected in B9 and the number of weeks is 120 in B10 to return a value 0.33

View attachment 122775

Please revisit your post. There are two "Area 3" rows and your example where area 2 and 120 weeks gives 0.33 seems wrong t

Apologies, i wrote the question, then changed some data! the example of area 2 with 120 weeks would return cell C4 -0.2

 

[Peter_SSs]

Thanks for updating your profile.
(Just need XL2BB next time then you'd get an example of your size & data rather than a small one like this. )
However, see if this is the sort of thing you are after.

25 02 27.xlsm
	A	B	C	D
1
2		104	156	208
3	Area 1	5	2	9
4	Area 2	6	4	7
5	Area 3	3	8	0
6
7
8
9	Area	Area 2
10	Week	120
11	Result	4
Sheet7

Cell Formulas
Range	Formula
B11	B11	=INDEX(B3:D5,XMATCH(B9,A3:A5),XMATCH(B10,B2:D2,1))

 

[clueless48]

Thanks for updating your profile.
(Just need XL2BB next time then you'd get an example of your size & data rather than a small one like this. )
However, see if this is the sort of thing you are after.

25 02 27.xlsm
	A	B	C	D
1
2		104	156	208
3	Area 1	5	2	9
4	Area 2	6	4	7
5	Area 3	3	8	0
6
7
8
9	Area	Area 2
10	Week	120
11	Result	4
Sheet7

Cell Formulas
Range	Formula
B11	B11	=INDEX(B3:D5,XMATCH(B9,A3:A5),XMATCH(B10,B2:D2,1))

[

Cell Formulas
Range	Formula

Thank you, this works perfectly !

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.