Problems With an "If" Formula

[Jingles3X]

Hi. I recently lost my job, and am returning to the world of Self Employed, in a service business.
As part of setting this business up, I am creating a flat rate pricing system so that I can quickly
and easily update my pricing, while giving a visual aid for selling from the customer standpoint.

One of the components of the pricing system is the "Materials Multiplier"... If The part costs between
X and Y then multiply it by Z.

I have been trying for several days to figure this out, with no luck.
I can use this formula: "=IF(AND(AJ27>B27,AJ27<P27),AB27)" for a single line, but that only
helps if ALL parts fall within $0 and $10. I am uploading an image of the table that I have created.

Can anyone advise me as to how to have a cell evaluate the cost of a part against each line in this table,
then populate my materials column for each individual repair with the appropriate multiplier?

Thank you.

 

[Eric W]

Welcome to the MrExcel forum!

You can just use a VLOOKUP for that:

ME temp.xlsm
	C	D	E	F	G	H	I
1	If The Materials Cost is More than:	And Less Than:	The Multiplier Should Be:			Materials	Multiplier
2	0.00	10.00	5			28.37	4
3	10.01	25.00	5
4	25.01	50.00	4
5	50.01	100.00	4
6	100.01	200.00	3
7	200.01	350.00	2
8
9
Sheet36

Cell Formulas
Range	Formula
I2	I2	=VLOOKUP(H2,$C$2:$E$6,3)

 

[Jingles3X]

Welcome to the MrExcel forum!

You can just use a VLOOKUP for that:

ME temp.xlsm
	C	D	E	F	G	H	I
1	If The Materials Cost is More than:	And Less Than:	The Multiplier Should Be:			Materials	Multiplier
2	0.00	10.00	5			28.37	4
3	10.01	25.00	5
4	25.01	50.00	4
5	50.01	100.00	4
6	100.01	200.00	3
7	200.01	350.00	2
8
9
Sheet36

Cell Formulas
Range	Formula
I2	I2	=VLOOKUP(H2,$C$2:$E$6,3)

I’m. It sure I’m following how this would work for what I’m trying to accomplish.

 

[Eric W]

Perhaps I'm not following what you're asking. I believe you want to enter the amount in the Materials Cost column, then look up to see where that amount falls in your Multiplier Chart, then put the proper multiplier in the Materials Markup column? The VLOOKUP basically reads down the first column of your Chart until the Materials Cost is equal or greater than the amount in the first column, then returns the multiplier from the third column.

 

[Jingles3X]

I have been able to get individual formulas to work as indicated, but am having trouble nesting them all as one larger formula so that
each row of repairs with a materials cost can compare against all of the possible price points to identify the proper multiplier....

I cannot seem to get these to nest properly, as I continue to receive a #value error

 

[Eric W]

Seriously, you do NOT want to nest all those IFs! That's what the VLOOKUP (and other lookup functions) were created for. Can you show the sheet where your Multiplier table is, with row and column headings? If you have skipped lines or merged cell, that could be a problem. But based on your IFs, the formula would be something like:

=VLOOKUP(BA9,'Set-Up'!$B$27:$AB$43,27)


There is a tool here called XL2BB, which allows you to display your data in an easily readable form, better than just a picture. It's easy to download, install, and use. See the link in the reply box or my signature. If you could show your tables using it, it would be much easier to see what you're doing and to help you.

 

[Jingles3X]

Seriously, you do NOT want to nest all those IFs! That's what the VLOOKUP (and other lookup functions) were created for. Can you show the sheet where your Multiplier table is, with row and column headings? If you have skipped lines or merged cell, that could be a problem. But based on your IFs, the formula would be something like:

=VLOOKUP(BA9,'Set-Up'!$B$27:$AB$43,27)


There is a tool here called XL2BB, which allows you to display your data in an easily readable form, better than just a picture. It's easy to download, install, and use. See the link in the reply box or my signature. If you could show your tables using it, it would be much easier to see what you're doing and to help you.

Thank you! Here is a mini sheet of the first worksheet. the formula i believe would go in the BE column.

FRP-In Progress.xlsx
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ									AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
1
2
3
4
5
6					Plan Pricing																					Manually Enter				Auto Fills				Manually Enter				Auto Fills				Auto Fills
7	Standard Price								Secondary Repair				Plan Savings !													Time To Complete				Advantage Discount %				Materials Cost				Materials Markup				Warranty Reserve
8
9	290				246				195				43													1.00				0.85				$ 8.00				5.00				3%
10
11	276				235				181				41													0.50				0.85				$ 26.00				4.00				3%
12
13	404				343				309				61													0.75				0.85				$ 55.00				3.50				3%
14
15	526				447				431				79													0.75				0.85				$ 105.00				3.00				3%
16
17	836				711				741				125													0.75				0.85				$ 250.00				2.50				3%
18
19	961				817				866				144													0.75				0.85				$ 375.00				2.00				3%
20
21	1711				1455				1616				257													0.75				0.85				$ 1,200.00				1.25				3%
22
23	1931				1642				1836				290													0.75				0.85				$ 1,600.00				1.08				3%
24
25	211				180				116				32													0.75				0.85				$ 65.00								3%
26
31	211				180				116				32													0.75				0.85				$ 234.00								3%
32
33	211				180				116				32													0.75				0.85				$ 32.00								3%
34
35	211				180				116				32													0.75				0.85				$ 123.00								3%
36
37	211				180				116				32													0.75				0.85				$ 123.00								3%
38
39	211				180				116				32													0.75				0.85				$ 365.00								3%
40
41	211				180				116				32													0.75				0.85				$ 43.00								3%
42
43	211				180				116				32													0.75				0.85				$ 56.00								3%
44
45	211				180				116				32													0.75				0.85				$ 67.00								3%
46
47	211				180				116				32													0.75				0.85				$ 87.00								3%
48
49	211				180				116				32													0.75				0.85				$ 987.00								3%
50
51	211				180				116				32													0.75				0.85												3%
52
Flat Rate Book

Cell Formulas
Range	Formula
T9,T51,T49,T47,T45,T43,T41,T39,T37,T35,T33,T31,T25,T23,T21,T19,T17,T15,T13,T11	T9	=SUM((AO9*AS9)+(BA9*BE9)+BI9)+AK9
X9,X51,X49,X47,X45,X43,X41,X39,X37,X35,X33,X31,X25,X23,X21,X19,X17,X15,X13	X9	=SUM(T9*AW9)
AB9,AB51,AB49,AB47,AB45,AB43,AB41,AB39,AB37,AB35,AB33,AB31,AB25,AB23,AB21,AB19,AB17,AB15,AB13,AB11	AB9	=SUM(T9-AK9)
AF9,AF51,AF49,AF47,AF45,AF43,AF41,AF39,AF37,AF35,AF33,AF31,AF25,AF23,AF21,AF19,AF17,AF15,AF13,AF11	AF9	=SUM(T9-X9)
X11	X11	=SUM(T11*AW9)
BE9,BE19,BE17,BE15,BE13,BE11	BE9	=IF(AND(BA9>'Set-Up'!B27,BA9<'Set-Up'!P27),'Set-Up'!AB27)
BE21,BE23	BE21	=IF(AND(BA21>'Set-Up'!B41,BA21<'Set-Up'!P41),'Set-Up'!AB41)
AW9	AW9	='Set-Up'!AB50
AW11	AW11	='Set-Up'!AB50
AW13	AW13	='Set-Up'!AB50
AW15	AW15	='Set-Up'!AB50
AW17	AW17	='Set-Up'!AB50
AW19	AW19	='Set-Up'!AB50
AW21	AW21	='Set-Up'!AB50
AW23	AW23	='Set-Up'!AB50
AW25	AW25	='Set-Up'!AB50
AW31	AW31	='Set-Up'!AB50
AW33	AW33	='Set-Up'!AB50
AW35	AW35	='Set-Up'!AB50
AW37	AW37	='Set-Up'!AB50
AW39	AW39	='Set-Up'!AB50
AW41	AW41	='Set-Up'!AB50
AW43	AW43	='Set-Up'!AB50
AW45	AW45	='Set-Up'!AB50
AW47	AW47	='Set-Up'!AB50
AW49	AW49	='Set-Up'!AB50
AW51	AW51	='Set-Up'!AB50
BI9	BI9	='Set-Up'!AB17
BI11	BI11	='Set-Up'!AB17
BI13	BI13	='Set-Up'!AB17
BI15	BI15	='Set-Up'!AB17
BI17	BI17	='Set-Up'!AB17
BI19	BI19	='Set-Up'!AB17
BI21	BI21	='Set-Up'!AB17
BI23	BI23	='Set-Up'!AB17
BI25	BI25	='Set-Up'!AB17
BI31	BI31	='Set-Up'!AB17
BI33	BI33	='Set-Up'!AB17
BI35	BI35	='Set-Up'!AB17
BI37	BI37	='Set-Up'!AB17
BI39	BI39	='Set-Up'!AB17
BI41	BI41	='Set-Up'!AB17
BI43	BI43	='Set-Up'!AB17
BI45	BI45	='Set-Up'!AB17
BI47	BI47	='Set-Up'!AB17
BI49	BI49	='Set-Up'!AB17
BI51	BI51	='Set-Up'!AB17

 

[Jingles3X]

Seriously, you do NOT want to nest all those IFs! That's what the VLOOKUP (and other lookup functions) were created for. Can you show the sheet where your Multiplier table is, with row and column headings? If you have skipped lines or merged cell, that could be a problem. But based on your IFs, the formula would be something like:

=VLOOKUP(BA9,'Set-Up'!$B$27:$AB$43,27)


There is a tool here called XL2BB, which allows you to display your data in an easily readable form, better than just a picture. It's easy to download, install, and use. See the link in the reply box or my signature. If you could show your tables using it, it would be much easier to see what you're doing and to help you.

Here is the worksheet where i have the chart that demonstrates the markup multiplier...

FRP-In Progress.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
24
25	If The Materials Cost is More than:														And Less Than:												The Multiplier Should Be:
26
27	$ -														$ 25.00												5
28
29	$ 25.01														$ 50.00												4
30
31	$ 50.01														$ 100.00												3.5
32
33	$ 100.01														$ 200.00												3
34
35	$ 200.01														$ 350.00												2.5
36
37	$ 350.01														$ 500.00												2
38
39	$ 500.01														$ 1,000.00												1.5
40
41	$ 1,000.01														$ 1,500.00												1.25
42
43	$ 1,500.00														$ 200,000.00												1.075
44
Set-Up

 

[Eric W]

Consider:

ME temp.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
25	If The Materials Cost is More than:														And Less Than:												The Multiplier Should Be:						If The Materials Cost is More than:	And Less Than:	The Multiplier Should Be:
26																														Materials cost	Multiplier
27	0														25												5			347.99	2.5		0	25	5
28																																	25.01	50	4
29	25.01														50												4						50.01	100	3.5
30																																	100.01	200	3
31	50.01														100												3.5			Materials cost	Multiplier		200.01	350	2.5
32																														347.99	2.5		350.01	500	2
33	100.01														200												3						500.01	1000	1.5
34																																	1000.01	1500	1.25
35	200.01														350												2.5						1500	200000	1.075
36
37	350.01														500												2
38
39	500.01														1000												1.5
40
41	1000.01														1500												1.25
42
43	1500														200000												1.075
44
Set-up

Cell Formulas
Range	Formula
AF27	AF27	=VLOOKUP(AE27,'Set-up'!$AH$27:$AJ35,3)
AF32	AF32	=SUMPRODUCT('Set-up'!$AB$27:$AB$43,--('Set-up'!$B$27:$B$43<=AE32),--('Set-up'!$P$27:$P$43>=AE32))

I see you merged some cells in order to get an easy to read format. The problem is that merging cells plays havoc with most formulas. If you use a table with merged cells, you can't use VLOOKUP. I'd suggest using a table like the one in AH25:AJ35, and then change column widths, row heights, grid lines and/or color fill to format it the way you like. If you build the table like that, then the AF27 formula should work for you. If you keep the table as is, you'll have to use something more complicated, like the AF32 formula.

 

[Jingles3X]

I am not sure what I am doing wrong.... I am confident i put the formula in properly, but am getting an "#N/A" error

FRP-In Progress.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
28	Replace Clogged Nozzle
Flat Rate Book

FRP-In Progress.xlsx
	B	C	D	E
23	OH115.60
24	OH116.60
25	OH117.40
26	OH118.90
27	OH119.50
28	OH120.30
29	OH121.45
30	OH122.60
31
32
Flat Rate Book

FRP-In Progress.xlsx
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
5																		Pricing Calculation
6					Plan Pricing													Auto Fills	Auto Fills	Manually Enter	Auto Fills	Manually Enter	Auto Fills	Auto Fills
7	Standard Price								Secondary Repair				Plan Savings !					Diagnostic Charge	Labor Rate	Time To Complete	Advantage Discount %	Materials Cost	Materials Markup	Warranty Reserve
8
9	#N/A				#N/A				#N/A				#N/A					$ 95.00	$ 154.88	1.00	15%	$ 8.00	#N/A	3%
10	302				45				207				257					$ 95.00	$ 154.88	0.50	0.85	$ 26.00	5.00	3%
11	404				343				309				61					$ 95.00	$ 154.88	0.75	0.85	$ 55.00	3.50	3%
12	526				447				431				79					$ 95.00	$ 154.88	0.75	0.85	$ 105.00	3.00	3%
13	836				711				741				125					$ 95.00	$ 154.88	0.75	0.85	$ 250.00	2.50	3%
14	961				817				866				144					$ 95.00	$ 154.88	0.75	0.85	$ 375.00	2.00	3%
15	1711				1455				1616				257					$ 95.00	$ 154.88	0.75	0.85	$ 1,200.00	1.25	3%
16	1931				1642				1836				290					$ 95.00	$ 154.88	0.75	0.85	$ 1,600.00	1.08	3%
17	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 65.00		3%
18	211				179				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 134.00
19	211				179				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 65.00
20	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 234.00		3%
21	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 32.00		3%
22	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 123.00		3%
23	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 123.00		3%
24	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 365.00		3%
25	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 43.00		3%
26	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 56.00		3%
27	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 67.00		3%
28	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 87.00		3%
29	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85	$ 987.00		3%
30	211				180				116				32					$ 95.00	$ 154.88	0.75	0.85			3%
31
Flat Rate Book

Cell Formulas
Range	Formula
T9	T9	=SUM(AL9*AM9)+AO9*AP9
X9,X11:X30	X9	=SUM(T9*AN9)
AB9:AB30	AB9	=SUM(T9-AK9)
AF9:AF30	AF9	=SUM(T9-X9)
T10:T30	T10	=SUM((AL10*AM10)+(AO10*AP10)+AQ10)+AK10
X10	X10	=SUM(T10*AN9)
AK9	AK9	='Set-Up'!F11
AL9	AL9	='Labor Calculator'!R23
AK10	AK10	='Set-Up'!F11
AL10	AL10	='Labor Calculator'!R23
AK11	AK11	='Set-Up'!F11
AL11	AL11	='Labor Calculator'!R23
AK12	AK12	='Set-Up'!F11
AL12	AL12	='Labor Calculator'!R23
AK13	AK13	='Set-Up'!F11
AL13	AL13	='Labor Calculator'!R23
AK14	AK14	='Set-Up'!F11
AL14	AL14	='Labor Calculator'!R23
AK15	AK15	='Set-Up'!F11
AL15	AL15	='Labor Calculator'!R23
AK16	AK16	='Set-Up'!F11
AL16	AL16	='Labor Calculator'!R23
AK17	AK17	='Set-Up'!F11
AL17	AL17	='Labor Calculator'!R23
AK18	AK18	='Set-Up'!F11
AL18	AL18	='Labor Calculator'!R23
AK19	AK19	='Set-Up'!F11
AL19	AL19	='Labor Calculator'!R23
AK20	AK20	='Set-Up'!F11
AL20	AL20	='Labor Calculator'!R23
AK21	AK21	='Set-Up'!F11
AL21	AL21	='Labor Calculator'!R23
AK22	AK22	='Set-Up'!F11
AL22	AL22	='Labor Calculator'!R23
AK23	AK23	='Set-Up'!F11
AL23	AL23	='Labor Calculator'!R23
AK24	AK24	='Set-Up'!F11
AL24	AL24	='Labor Calculator'!R23
AK25	AK25	='Set-Up'!F11
AL25	AL25	='Labor Calculator'!R23
AK26	AK26	='Set-Up'!F11
AL26	AL26	='Labor Calculator'!R23
AK27	AK27	='Set-Up'!F11
AL27	AL27	='Labor Calculator'!R23
AK28	AK28	='Set-Up'!F11
AL28	AL28	='Labor Calculator'!R23
AK29	AK29	='Set-Up'!F11
AL29	AL29	='Labor Calculator'!R23
AK30	AK30	='Set-Up'!F11
AL30	AL30	='Labor Calculator'!R23
AP9	AP9	=VLOOKUP(AO9,'Set-Up'!C24:D32,3)
AP11:AP14	AP11	=IF(AND(AO11>'Set-Up'!B26,AO11<'Set-Up'!D26),'Set-Up'!E26)
AP15:AP16	AP15	=IF(AND(AO15>'Set-Up'!B31,AO15<'Set-Up'!D31),'Set-Up'!E31)
AQ9	AQ9	='Set-Up'!F15
AQ10	AQ10	='Set-Up'!F15
AQ11	AQ11	='Set-Up'!F15
AQ12	AQ12	='Set-Up'!F15
AQ13	AQ13	='Set-Up'!F15
AQ14	AQ14	='Set-Up'!F15
AQ15	AQ15	='Set-Up'!F15
AQ16	AQ16	='Set-Up'!F15
AQ17	AQ17	='Set-Up'!F15
AN10	AN10	='Set-Up'!F45
AN11	AN11	='Set-Up'!F45
AN12	AN12	='Set-Up'!F45
AN13	AN13	='Set-Up'!F45
AN14	AN14	='Set-Up'!F45
AN15	AN15	='Set-Up'!F45
AN16	AN16	='Set-Up'!F45
AN17	AN17	='Set-Up'!F45
AN18	AN18	='Set-Up'!F45
AN19	AN19	='Set-Up'!F45
AN20	AN20	='Set-Up'!F45
AN21	AN21	='Set-Up'!F45
AN22	AN22	='Set-Up'!F45
AN23	AN23	='Set-Up'!F45
AN24	AN24	='Set-Up'!F45
AN25	AN25	='Set-Up'!F45
AN26	AN26	='Set-Up'!F45
AN27	AN27	='Set-Up'!F45
AN28	AN28	='Set-Up'!F45
AN29	AN29	='Set-Up'!F45
AN30	AN30	='Set-Up'!F45
AQ20	AQ20	='Set-Up'!F15
AQ21	AQ21	='Set-Up'!F15
AQ22	AQ22	='Set-Up'!F15
AQ23	AQ23	='Set-Up'!F15
AQ24	AQ24	='Set-Up'!F15
AQ25	AQ25	='Set-Up'!F15
AQ26	AQ26	='Set-Up'!F15
AQ27	AQ27	='Set-Up'!F15
AQ28	AQ28	='Set-Up'!F15
AQ29	AQ29	='Set-Up'!F15
AQ30	AQ30	='Set-Up'!F15