0

Cost Model

Bps

I'm trying to calculate the J29 cell from Cost Model to output using the rates in Bps, where J29 in Cost Model should actually be $42,363.6. This math comes from using [.0025 x (Bps D3 - Cost Model I25)] + [.002 x (J27-(Bps D3 - Cost Model I25))]

Is this feasible? Is there a formula that can calculate this?

Here are the formulas in Cost Model currently:

Cell J25: `=SUM(I25+J27)`
Cell J27: input by me
Cell J28: word-break: break-all;

=IF(J25<='Bps Breakdown'!$D$3,'Bps Breakdown'!$F$3,IF('Cost Model'!J25<='Bps Breakdown'!$D$5,'Bps Breakdown'!$F$5,IF('Cost Model'!J25>'Bps Breakdown'!$D$6,'Bps Breakdown'!$F$6)))

Cell J29: `=(J27*J28)`
pnuts
  • 58,317
  • 11
  • 87
  • 139
Zach Hales
  • 13
  • 6
  • Yes, I think so. Are these in the same workbook or different workbooks? If the same,what are the name of the worksheets. If different workbooks, what are the names of the workbooks and worksheets for image 1 and 2? – Hambone Oct 12 '15 at 21:47
  • Same workbook, two different tabs. Is there a way I can attach the workbook to this? – Zach Hales Oct 13 '15 at 13:10
  • Apparently I cannot add the actual file unless I link it to a shared site (dropbox, etc) – Zach Hales Oct 13 '15 at 13:30

1 Answers1

0

I may have oversimplified this, but does this work?

=I28*('Bps Breakdown'!D3-'Cost Model'!I25)+
 J28*('Cost Model'!J27-('Bps Breakdown'!D3-'Cost Model'!I25))
Hambone
  • 15,600
  • 8
  • 46
  • 69
  • Thank you for this. It works ... but my issue comes up that I need this to work going forward by month. If I input that into I29, I obviously don't get the correct output because it can't pull from any previous cells. It does work though. The problem also comes that after I reach another threshold, i have to change the percentage again. I don't know if excel has the logic to say "At x amount, use x rate. At y amount, use y rate. At z amount use z rate."? – Zach Hales Oct 13 '15 at 14:05
  • I actually wondered about that. Yes, you can do that. It would involve restructuring your data a little, but it sounds quite doable. I didn't actually dive that deeply into your model (since I didn't have a spreadsheet to work with), but I can probably mock it up based on your samples. In a nutshell, I think a `vlookup` would work nicely here. I'll see if I can't find a way to show you what I had in mind. – Hambone Oct 13 '15 at 22:19
  • Really appreciate it. You can email me me at zach.hales@greenphire.com if you want to connect and go through the actual document. – Zach Hales Oct 14 '15 at 14:50
  • Thanks for the excellent work and advice http://stackoverflow.com/users/1278553/hambone! – Zach Hales Oct 16 '15 at 14:21