Trying to configure if/and then formula based on multiple criteria

[Kaz09]

I am trying to create a formula to determine the result of a lookup table based on two criteria. Eg. If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6 to calculate sum.

	Single Book Target	Single Books Sold	Percentage of Sold Boks	No. of Book Sets Target	No. of Book Sets old	Percentage Book Set Sales		% Volume Bonus Single Books	Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable
	7	7	100.00%	50	59	118.00%			70%	101%	150+%
								90.00%	£300.00	£500.00	£750.00
								100.00%	£450.00	£800.00	£1,050.00
								115.00%	£550.00	£950.00	£1,200.00
	Examples of what I am trying to calculate
	If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6	Answer should be: £17,700

Any guidance will be greatly appreciated

 

[etaf]

not sure what columns you have as a table - and did not seem to line up

but using the words

If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6

=IF ( AND ( F5 > = 0.9 , F5 < 1 , C5 > = 0.7 , C5 < 1.01 ) , F5*J6 , "")

=IF(AND(F5>0.9,F5<1,C5>0.7,C5<1.01),F5*J6,"")

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed


I have changed the table into XL2BB

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Single Book Target	Single Books Sold	Percentage of Sold Boks	No. of Book Sets	No. of Book Sets	Percentage Book Set Sales		% Volume Bonus Single Books	Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable
2					Target	old
3		7	7	100.00%	50	59	118.00%			70%	101%	150+%
4									90.00%	$300.00	$500.00	$750.00
5									100.00%	$450.00	$800.00	$1,050.00
6									115.00%	$550.00	$950.00	$1,200.00
7
8		Examples of what I am trying to calculate
9
10		If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6	Answer should be: £17,700
11
12	Any guidance will be greatly appreciated
13	Like Quote Reply
14
Sheet1

Cell Formulas
Range	Formula
I12	I12	=IF(AND(F5>0.9,F5<1,C5>0.7,C5<1.01),F5*J6,"")

 

[Kaz09]

Hi etaf, thank you for coming back. Sorry for not adding this correctly, I tried adding the spreadsheet but didn't work so going to try again.

I don't think I explained what I am trying to do very well so sorry for that.

I have been tasked with the following:
Depending on the percentage of book sets (G5) sold & percentage of single books sold (D5) will depend on what commission rate will apply (J6:L8) multiplied by the number of book sets sold (F5)

I tried your formula but couldn't get it to work. Hopefully I have added a mini table using the XL2bb correctly although when I preview it the columns seem really large Hopefully I have done this correctly.

I appreciate you taking time out to help solve my issue.

Books Sold.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2
3		Single Book Target	Single Books Sold	Percentage of Single Books Sold	No. of Book Sets Target	No. of Book Sets Sold	Percentage of Book Sets Sold		% Volume Bonus Single Books	Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable
4										70%	101%	150+%
5	Quarter 1	3	3		20	20			90.00%	£300.00	£500.00	£750.00	=IF(OR(AND(F5>=90%,F5<100%),AND(C5>=70%,C5<101%)),F5*J6),IF(OR(AND(F5>=100%,F5<115%),AND(C5>=70%,C5<101%)),F5*J7)
6		2	1		15	20			100.00%	£450.00	£800.00	£1,050.00
7		2	2		15	19			115.00%	£550.00	£950.00	£1,200.00
8		7	6	85.71%	50	59	118.00%		Qtr 1
9	Quarter 2	3	3		20	25			90.00%	£300.00	£500.00	£750.00
10		2	5		15	30			100.00%	£450.00	£800.00	£1,050.00
11		2	2		15	23			115.00%	£550.00	£950.00	£1,200.00
12		7	10	142.86%	50	78	156.00%		Qtr 2
13
14
15		Examples of what I am trying to calculate
16
17		If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6									Answer should be: £17,700
18
19		If F5 >=90% but less than 100% and C5 >= 101% but less than 150% then multiple F5 by K6									Answer should be: £29,500
20
21		If F5 >=90% but less than 100% and C5 >= 150% then multiple F5 by L6									Answer should be: £44,250
22
23		IF F5 >=100% but less than 115% and C5 >= 70% but less than 101% then multiple F5 by J7									Answer should be: £26,550
24
25		IF F5 >=100% but less than 115% and C5 >= 101% but less than 150% then multiple F5 by K7									Answer should be: £47,200
26
Sheet1

Cell Formulas
Range	Formula
E12:F12,B12:C12,E8:F8,B8:C8	B8	=SUM(B5:B7)
D8,G12,D12,G8	D8	=C8/B8

 

[etaf]

column F =
No. of Book Sets Sold

So how is the % worked out ?

If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6

F5 is a number and NOT a percent

column C is Single Books Sold
so C5 is NOT a percent

i think we need more info on how the % is worked out for F5 and C5

 

[Kaz09]

Hi etaf, my apologies. I am trying to achieve the bonus paid per quarter based on the below:

The percentage of book sets sold for qtr 1 is 118% (F8 / E8 = G8) and the percentage of single books sold in qtr 1 is 85.71% (C8 / B8 = D8).
Based on the above, I then need to look up cell range I5:L7 to identify the amount payable per book set. The result would be J7 (Percentage of book sets sold 118% - look up row 7) and the percentage of single books sold is 85.71% - look up column J.
I then multiple J7 by the actual number of book sets sold in cell F8 - Bonus payable £550 (J7) x 59 (F8) =

I hope the above is a little clearer.

Thank you again for trying to help.

 

[etaf]

so we need to do a GRID lookup
to get the value
Q1
=INDEX($J5:$L7,MATCH(G8,$I5:$I7,1),MATCH(D8,$J$4:$L$4,1))
550
F8 = 59
550 * 59 = 32450

Q2
=INDEX($J9:$L11,MATCH(G12,$I9:$I11,1),MATCH(D12,$J$4:$L$4,1))
950
F8 = 78
950*78 = 74100

does that work ?

Merged cells are a pain for formulas - I have added the results in column N and O

try your different values and see if it gives the correct results - if not
let us know

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Single Book Target	Single Books Sold	Percentage of Single Books Sold	No. of Book Sets Target	No. of Book Sets Sold	Percentage of Book Sets Sold		% Volume Bonus Single Books	Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable	Volumn Bonus amount	Volume Bonus Payable
4										0.7	1.01	150+%
5	Quarter 1	3	3		20	20			0.9	300	500	750	#VALUE!
6		2	1		15	20			1	450	800	1050
7		2	2		15	19			1.15	550	950	1200
8		7	6	86%	50	59	118%		Qtr 1					550	32450
9	Quarter 2	3	3		20	25			0.9	300	500	750
10		2	5		15	30			1	450	800	1050
11		2	2		15	23			1.15	550	950	1200
12		7	10	143%	50	78	156%		Qtr 2					950	74100
13
Sheet2

Cell Formulas
Range	Formula
M5	M5	=IF(OR(AND(F5>=90%,F5<100%),AND(C5>=70%,C5<101%)),F5*J6),IF(OR(AND(F5>=100%,F5<115%),AND(C5>=70%,C5<101%)),F5*J7)
B8:C8,E8:F8,B12:C12,E12:F12	B8	=SUM(B5:B7)
D8,G8,D12,G12	D8	=C8/B8
N8,N12	N8	=INDEX($J5:$L7,MATCH(G8,$I5:$I7,1),MATCH(D8,$J$4:$L$4,1))
O8,O12	O8	=N8*F8

 

[etaf]

i had to change and add in zero for the horizontal and vertical lookup - also change the 150%+ to 1.5
otherwise it didnt work for above 150% or below the threshold

We could change - so a N/A error would be a zero - if you dont want the zeros added

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Single Book Target	Single Books Sold	Percentage of Single Books Sold	No. of Book Sets Target	No. of Book Sets Sold	Percentage of Book Sets Sold		% Volume Bonus Single Books		Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable	Volumn Bonus amount	Volume Bonus Payable
4										0	0.7	1.01	1.5
5									0	0	0	0	0
6	Quarter 1	3	3		20	20			0.9	0	300	500	750	#VALUE!
7		2	1		15	20			1	0	450	800	1050
8		2	2		15	19			1.15	0	550	950	1200
9		7	6	200%	50	59	90%		Qtr 1						750	44250
10									0	0	0	0	0
11	Quarter 2	3	3		20	25			0.9	0	300	500	750
12		2	5		15	30			1	0	450	800	1050
13		2	2		15	23			1.15	0	550	950	1200
14		7	10	35%	50	78	156%		Qtr 2						0	0
15
Sheet2

Cell Formulas
Range	Formula
N6	N6	=IF(OR(AND(F6>=90%,F6<100%),AND(C6>=70%,C6<101%)),F6*K7),IF(OR(AND(F6>=100%,F6<115%),AND(C6>=70%,C6<101%)),F6*K8)
B9:C9,E14:F14,B14:C14,E9:F9	B9	=SUM(B6:B8)
O9,O14	O9	=INDEX($J5:$M8,MATCH(G9,$I5:$I8,1),MATCH(D9,$J$4:$M$4,1))
P9,P14	P9	=O9*F9
G14	G14	=F14/E14

using an IFERROR to give a zero

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3		Single Book Target	Single Books Sold	Percentage of Single Books Sold	No. of Book Sets Target	No. of Book Sets Sold	Percentage of Book Sets Sold		% Volume Bonus Single Books	Volume Bonus Amount Book Sets (70-100%)	Volume Bonus Amount Book Sets (101-150%)	Volume Bonus Amount Book Sets (150%+)	Volume Bonus Payable	Volumn Bonus amount	Volume Bonus Payable
4										0.7	1.01	1.5
5	Quarter 1	3	3		20	20			0.9	300	500	750	#VALUE!
6		2	1		15	20			1	450	800	1050
7		2	2		15	19			1.15	550	950	1200
8		7	6	200%	50	59	90%		Qtr 1					750	44250
9	Quarter 2	3	3		20	25			0.9	300	500	750
10		2	5		15	30			1	450	800	1050
11		2	2		15	23			1.15	550	950	1200
12		7	10	35%	50	78	156%		Qtr 2					0	0
13
Sheet3

Cell Formulas
Range	Formula
M5	M5	=IF(OR(AND(F5>=90%,F5<100%),AND(C5>=70%,C5<101%)),F5*J6),IF(OR(AND(F5>=100%,F5<115%),AND(C5>=70%,C5<101%)),F5*J7)
B8:C8,E12:F12,B12:C12,E8:F8	B8	=SUM(B5:B7)
N8,N12	N8	=IFERROR(INDEX($J5:$L7,MATCH(G8,$I5:$I7,1),MATCH(D8,$J$4:$L$4,1)),0)
O8,O12	O8	=N8*F8
G12	G12	=F12/E12

 

[Kaz09]

Hi, etaf,

That is amazing, thank you so much for your very speedy reply. I'm in awe of what you can do. It works a treat and will make my job so much easier. If I wanted to change the 0 to N/A what would I need to change?

Thanks,
Kaz

 

[etaf]

change
=IFERROR(INDEX($J5:$L7,MATCH(G8,$I5:$I7,1),MATCH(D8,$J$4:$L$4,1)),0)
to
=INDEX($J5:$L7,MATCH(G8,$I5:$I7,1),MATCH(D8,$J$4:$L$4,1))

 

[Kaz09]

Thank Etaf, I will give that a go.