Problems With an "If" Formula

Jingles3X

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Attachments

  • Untitled.png
    Untitled.png
    67 KB · Views: 4

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,491
Welcome to the MrExcel forum!

You can just use a VLOOKUP for that:

ME temp.xlsm
CDEFGHI
1If The Materials Cost is More than:And Less Than:The Multiplier Should Be:MaterialsMultiplier
20.0010.00528.374
310.0125.005
425.0150.004
550.01100.004
6100.01200.003
7200.01350.002
8
9
Sheet36
Cell Formulas
RangeFormula
I2I2=VLOOKUP(H2,$C$2:$E$6,3)
 

Jingles3X

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
Welcome to the MrExcel forum!

You can just use a VLOOKUP for that:

ME temp.xlsm
CDEFGHI
1If The Materials Cost is More than:And Less Than:The Multiplier Should Be:MaterialsMultiplier
20.0010.00528.374
310.0125.005
425.0150.004
550.01100.004
6100.01200.003
7200.01350.002
8
9
Sheet36
Cell Formulas
RangeFormula
I2I2=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,491
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

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
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....


1634753843404.png


1634753927442.png


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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,491
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

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
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
TUVWXYZAAABACADAEAFAGAHAIAJASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBL
1
2
3
4
5
6Plan PricingManually EnterAuto FillsManually EnterAuto FillsAuto Fills
7Standard PriceSecondary RepairPlan Savings !Time To CompleteAdvantage Discount %Materials CostMaterials MarkupWarranty Reserve
8
9290246195431.000.85$ 8.005.003%
10
11276235181410.500.85$ 26.004.003%
12
13404343309610.750.85$ 55.003.503%
14
15526447431790.750.85$ 105.003.003%
16
178367117411250.750.85$ 250.002.503%
18
199618178661440.750.85$ 375.002.003%
20
211711145516162570.750.85$ 1,200.001.253%
22
231931164218362900.750.85$ 1,600.001.083%
24
25211180116320.750.85$ 65.003%
26
31211180116320.750.85$ 234.003%
32
33211180116320.750.85$ 32.003%
34
35211180116320.750.85$ 123.003%
36
37211180116320.750.85$ 123.003%
38
39211180116320.750.85$ 365.003%
40
41211180116320.750.85$ 43.003%
42
43211180116320.750.85$ 56.003%
44
45211180116320.750.85$ 67.003%
46
47211180116320.750.85$ 87.003%
48
49211180116320.750.85$ 987.003%
50
51211180116320.750.853%
52
Flat Rate Book
Cell Formulas
RangeFormula
T9,T51,T49,T47,T45,T43,T41,T39,T37,T35,T33,T31,T25,T23,T21,T19,T17,T15,T13,T11T9=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,X13X9=SUM(T9*AW9)
AB9,AB51,AB49,AB47,AB45,AB43,AB41,AB39,AB37,AB35,AB33,AB31,AB25,AB23,AB21,AB19,AB17,AB15,AB13,AB11AB9=SUM(T9-AK9)
AF9,AF51,AF49,AF47,AF45,AF43,AF41,AF39,AF37,AF35,AF33,AF31,AF25,AF23,AF21,AF19,AF17,AF15,AF13,AF11AF9=SUM(T9-X9)
X11X11=SUM(T11*AW9)
BE9,BE19,BE17,BE15,BE13,BE11BE9=IF(AND(BA9>'Set-Up'!B27,BA9<'Set-Up'!P27),'Set-Up'!AB27)
BE21,BE23BE21=IF(AND(BA21>'Set-Up'!B41,BA21<'Set-Up'!P41),'Set-Up'!AB41)
AW9AW9='Set-Up'!AB50
AW11AW11='Set-Up'!AB50
AW13AW13='Set-Up'!AB50
AW15AW15='Set-Up'!AB50
AW17AW17='Set-Up'!AB50
AW19AW19='Set-Up'!AB50
AW21AW21='Set-Up'!AB50
AW23AW23='Set-Up'!AB50
AW25AW25='Set-Up'!AB50
AW31AW31='Set-Up'!AB50
AW33AW33='Set-Up'!AB50
AW35AW35='Set-Up'!AB50
AW37AW37='Set-Up'!AB50
AW39AW39='Set-Up'!AB50
AW41AW41='Set-Up'!AB50
AW43AW43='Set-Up'!AB50
AW45AW45='Set-Up'!AB50
AW47AW47='Set-Up'!AB50
AW49AW49='Set-Up'!AB50
AW51AW51='Set-Up'!AB50
BI9BI9='Set-Up'!AB17
BI11BI11='Set-Up'!AB17
BI13BI13='Set-Up'!AB17
BI15BI15='Set-Up'!AB17
BI17BI17='Set-Up'!AB17
BI19BI19='Set-Up'!AB17
BI21BI21='Set-Up'!AB17
BI23BI23='Set-Up'!AB17
BI25BI25='Set-Up'!AB17
BI31BI31='Set-Up'!AB17
BI33BI33='Set-Up'!AB17
BI35BI35='Set-Up'!AB17
BI37BI37='Set-Up'!AB17
BI39BI39='Set-Up'!AB17
BI41BI41='Set-Up'!AB17
BI43BI43='Set-Up'!AB17
BI45BI45='Set-Up'!AB17
BI47BI47='Set-Up'!AB17
BI49BI49='Set-Up'!AB17
BI51BI51='Set-Up'!AB17
 

Jingles3X

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
24
25If The Materials Cost is More than:And Less Than:The Multiplier Should Be:
26
27$ -$ 25.005
28
29$ 25.01$ 50.004
30
31$ 50.01$ 100.003.5
32
33$ 100.01$ 200.003
34
35$ 200.01$ 350.002.5
36
37$ 350.01$ 500.002
38
39$ 500.01$ 1,000.001.5
40
41$ 1,000.01$ 1,500.001.25
42
43$ 1,500.00$ 200,000.001.075
44
Set-Up
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,491
Consider:

ME temp.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
25If 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:
26Materials costMultiplier
270255347.992.50255
2825.01504
2925.0150450.011003.5
30100.012003
3150.011003.5Materials costMultiplier200.013502.5
32347.992.5350.015002
33100.012003500.0110001.5
341000.0115001.25
35200.013502.515002000001.075
36
37350.015002
38
39500.0110001.5
40
411000.0115001.25
42
4315002000001.075
44
Set-up
Cell Formulas
RangeFormula
AF27AF27=VLOOKUP(AE27,'Set-up'!$AH$27:$AJ35,3)
AF32AF32=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

New Member
Joined
Oct 20, 2021
Messages
34
Office Version
  1. 2013
Platform
  1. Windows
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
FGHIJKLMNOPQRS
28Replace Clogged Nozzle
Flat Rate Book

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

FRP-In Progress.xlsx
TUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
5Pricing Calculation
6Plan PricingAuto FillsAuto FillsManually EnterAuto FillsManually EnterAuto FillsAuto Fills
7Standard PriceSecondary RepairPlan Savings !Diagnostic ChargeLabor RateTime To CompleteAdvantage Discount %Materials CostMaterials MarkupWarranty Reserve
8
9#N/A#N/A#N/A#N/A$ 95.00$ 154.881.0015%$ 8.00#N/A3%
1030245207257$ 95.00$ 154.880.500.85$ 26.005.003%
1140434330961$ 95.00$ 154.880.750.85$ 55.003.503%
1252644743179$ 95.00$ 154.880.750.85$ 105.003.003%
13836711741125$ 95.00$ 154.880.750.85$ 250.002.503%
14961817866144$ 95.00$ 154.880.750.85$ 375.002.003%
15171114551616257$ 95.00$ 154.880.750.85$ 1,200.001.253%
16193116421836290$ 95.00$ 154.880.750.85$ 1,600.001.083%
1721118011632$ 95.00$ 154.880.750.85$ 65.003%
1821117911632$ 95.00$ 154.880.750.85$ 134.00
1921117911632$ 95.00$ 154.880.750.85$ 65.00
2021118011632$ 95.00$ 154.880.750.85$ 234.003%
2121118011632$ 95.00$ 154.880.750.85$ 32.003%
2221118011632$ 95.00$ 154.880.750.85$ 123.003%
2321118011632$ 95.00$ 154.880.750.85$ 123.003%
2421118011632$ 95.00$ 154.880.750.85$ 365.003%
2521118011632$ 95.00$ 154.880.750.85$ 43.003%
2621118011632$ 95.00$ 154.880.750.85$ 56.003%
2721118011632$ 95.00$ 154.880.750.85$ 67.003%
2821118011632$ 95.00$ 154.880.750.85$ 87.003%
2921118011632$ 95.00$ 154.880.750.85$ 987.003%
3021118011632$ 95.00$ 154.880.750.853%
31
Flat Rate Book
Cell Formulas
RangeFormula
T9T9=SUM(AL9*AM9)+AO9*AP9
X9,X11:X30X9=SUM(T9*AN9)
AB9:AB30AB9=SUM(T9-AK9)
AF9:AF30AF9=SUM(T9-X9)
T10:T30T10=SUM((AL10*AM10)+(AO10*AP10)+AQ10)+AK10
X10X10=SUM(T10*AN9)
AK9AK9='Set-Up'!F11
AL9AL9='Labor Calculator'!R23
AK10AK10='Set-Up'!F11
AL10AL10='Labor Calculator'!R23
AK11AK11='Set-Up'!F11
AL11AL11='Labor Calculator'!R23
AK12AK12='Set-Up'!F11
AL12AL12='Labor Calculator'!R23
AK13AK13='Set-Up'!F11
AL13AL13='Labor Calculator'!R23
AK14AK14='Set-Up'!F11
AL14AL14='Labor Calculator'!R23
AK15AK15='Set-Up'!F11
AL15AL15='Labor Calculator'!R23
AK16AK16='Set-Up'!F11
AL16AL16='Labor Calculator'!R23
AK17AK17='Set-Up'!F11
AL17AL17='Labor Calculator'!R23
AK18AK18='Set-Up'!F11
AL18AL18='Labor Calculator'!R23
AK19AK19='Set-Up'!F11
AL19AL19='Labor Calculator'!R23
AK20AK20='Set-Up'!F11
AL20AL20='Labor Calculator'!R23
AK21AK21='Set-Up'!F11
AL21AL21='Labor Calculator'!R23
AK22AK22='Set-Up'!F11
AL22AL22='Labor Calculator'!R23
AK23AK23='Set-Up'!F11
AL23AL23='Labor Calculator'!R23
AK24AK24='Set-Up'!F11
AL24AL24='Labor Calculator'!R23
AK25AK25='Set-Up'!F11
AL25AL25='Labor Calculator'!R23
AK26AK26='Set-Up'!F11
AL26AL26='Labor Calculator'!R23
AK27AK27='Set-Up'!F11
AL27AL27='Labor Calculator'!R23
AK28AK28='Set-Up'!F11
AL28AL28='Labor Calculator'!R23
AK29AK29='Set-Up'!F11
AL29AL29='Labor Calculator'!R23
AK30AK30='Set-Up'!F11
AL30AL30='Labor Calculator'!R23
AP9AP9=VLOOKUP(AO9,'Set-Up'!C24:D32,3)
AP11:AP14AP11=IF(AND(AO11>'Set-Up'!B26,AO11<'Set-Up'!D26),'Set-Up'!E26)
AP15:AP16AP15=IF(AND(AO15>'Set-Up'!B31,AO15<'Set-Up'!D31),'Set-Up'!E31)
AQ9AQ9='Set-Up'!F15
AQ10AQ10='Set-Up'!F15
AQ11AQ11='Set-Up'!F15
AQ12AQ12='Set-Up'!F15
AQ13AQ13='Set-Up'!F15
AQ14AQ14='Set-Up'!F15
AQ15AQ15='Set-Up'!F15
AQ16AQ16='Set-Up'!F15
AQ17AQ17='Set-Up'!F15
AN10AN10='Set-Up'!F45
AN11AN11='Set-Up'!F45
AN12AN12='Set-Up'!F45
AN13AN13='Set-Up'!F45
AN14AN14='Set-Up'!F45
AN15AN15='Set-Up'!F45
AN16AN16='Set-Up'!F45
AN17AN17='Set-Up'!F45
AN18AN18='Set-Up'!F45
AN19AN19='Set-Up'!F45
AN20AN20='Set-Up'!F45
AN21AN21='Set-Up'!F45
AN22AN22='Set-Up'!F45
AN23AN23='Set-Up'!F45
AN24AN24='Set-Up'!F45
AN25AN25='Set-Up'!F45
AN26AN26='Set-Up'!F45
AN27AN27='Set-Up'!F45
AN28AN28='Set-Up'!F45
AN29AN29='Set-Up'!F45
AN30AN30='Set-Up'!F45
AQ20AQ20='Set-Up'!F15
AQ21AQ21='Set-Up'!F15
AQ22AQ22='Set-Up'!F15
AQ23AQ23='Set-Up'!F15
AQ24AQ24='Set-Up'!F15
AQ25AQ25='Set-Up'!F15
AQ26AQ26='Set-Up'!F15
AQ27AQ27='Set-Up'!F15
AQ28AQ28='Set-Up'!F15
AQ29AQ29='Set-Up'!F15
AQ30AQ30='Set-Up'!F15
 

Forum statistics

Threads
1,175,769
Messages
5,899,382
Members
434,766
Latest member
PremPrakash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top