total cost based on a value that falls within specific value

[HVACPartsGUy]

I need help creating a formula that will return the total cost of an item based on a specific valuation.

I think part of it is a VLOOKUP, but I am getting a REF error.

I am also not sure, how to write the next steps of the formula.

I have included notes in the attached excel file to help explain what I need to do.

[ChemistB]

Okay, I think I know what you are trying to do.
For the first part, you're trying to return from column E (4) instead of D (3) so modify the base equation

Please Login or Register  to view this content.

Then you want to add the additional amount per 1000 from Column F. That would be Q3 minus the minimum (i.e. 25000 in this case) * Col F

Please Login or Register  to view this content.

So

Please Login or Register  to view this content.

Not sure what rows 17-22 are for or if there are any other fees.

[HVACPartsGUy]

Thank you quick response, I really appreciate your help.

Unfortunately, your formula did not return the correct result.

I have updated the file to show what the permit costs would be based on the current valuation. Your results were off by $164.00.

Lines 17 - 22 are the permit costs for another city. I have to use a similar formula for each city where we have projects. I am hoping that once I have a formula, that I will be able to tweak it slightly to work for each city or county in which we have a project.

Ultimately, I would like these results to report values back to the estimate workbook for our project managers, but that can wait for now.

[XLent]

so, perhaps:

=SUM($Q$3,($Q$3-(LOOKUP($Q$3,$B$7:$B$10)-1))/1000*LOOKUP($Q$3,$B$7:$B$10,$F$7:$F$10),LOOKUP($Q$3,$B$7:$B$10,$L$7:$L$10),$M$12)

note your expected result is out by 50c (J12 should be 115.5 rather than 115)

[HVACPartsGUy]

We are on the right track. Thank you.

However, there is an error, which is returning the incorrect total with permit costs.

The total job cost should be $126.75 less.

In cell O7, The value shown is $40,798.17 on a $40,000 valuation.

The total permit costs are only $671.42, which means the total valuation should be $40,671.42.

I have uploaded the updated document this reply,

Thank you again for all your help.

[XLent]

the difference now is the fact that the incremental value has been added to column G, and thus now included in K, whereas previously it was not being calculated

given above it follows that the previous formula is going to double count this element as it is calculating the value now being stored in G, and adding this to value in K which is inclusive of G

so, simply, remove the incremental calculation... the below would give you the aggregate of the base + permit total w/o documents

=SUM($P$3,LOOKUP($P$3,$B$7:$B$10,$K$7:$K$10),$L$11)

I am still dubious about the pages element as it's not clear to me how that is tracked so, for now, so left as reference to $L$11
it doesn't seem practical for an end-user to be entering this value into whichever of these matrices {and bands} is relative to the base valuation