Using Date ranges to come up with a formula to figure out a discount schedule

[ellow]

Thank you for any help you can provide I will try to be as detailed as I possibly can be..
On My spread sheet, I have 3 tabs at the bottom listed as "Main Page" "DP Schedule" & "SpreadValues"

The "Main Page" looks like this

Column A = Customer Name
Column B = Contract
Column C = Basis @ Delivery
Column D = Basis When Sold
Column E = Date Product Was Delivered
Column F = Date Product was Sold
Column G = DP Charges That Occurred
Column H = Accumulated Spread Value
Column I = Gain/Loss

Sheet "Main Paige" Column E, represents "Date Product was brought in"
Sheet "Main page" Column F, represents "Date Product was sold"

Sheet "Main Page" Column G, represents "DP charges that Occurred"

-This column needs to be calculated based off of sheet2 renamed as
"DP Schedule"

-Basically, at each facility, the DP charges can vary. Some plants might charge a flat fee if the product is brought in during a certain period of time and then so many cents per month there after... or there will be plants that don't charge a flat fee and only charge so many cents per month.

-So the formula that needs to be entered on the "Main Page" column G, needs to based off the tab listed as "DP Schedule"

-If the date that was typed in on column E on the main page falls between the date range that is typed in Cells B4 & C4 on the DP schedule sheet then that would represent that product will have a flat fee of so many cents, what ever is typed in Cell A4....

-If the date that was typed in on Column F on the main page falls between the date range that is typed in Cells B4 & C4 on the DP Schedule sheet then Column "G's" answer would be what ever is typed in Cell A4.

-If the date typed in Column F on the main page does NOT fall with in the dates typed in Cells B4 & C4 on the DP Schedule sheet, Then Column G's answer should be the flat fee that is typed in Cell A4 plus the value that is typed in Cell D4

The difference between a flat fee and Cell D4, Per/month fee..is the flat fee does not get prorated...so now matter if the product is there 5 days or 20 days it is still what ever value is typed in Cell A4...Now the value that gets typed in cell D4 on the DP schedule sheet is prorated by the days in a month...So if the product was here for 15 days they would only get charged 2 1/2 cents....Ok so my 2 examples go like this...

Cell B4 = 9/15/14 & cell C4 = 12/31/14 & Cell A4 = .15 cents

Ex 1) producer bring us there product on 10/11/14 and then turns around and sells it on 11/18/14, Column G should = .15 cents

Ex 2) producer brings us there product on 10/11/14 and sells it on 3/15/15...Column G should = .15 cents + .05 cents per month prorated = .2750 cents

Ex 3) producer brings us there product on 3/1/15 and sells it on 4/1/15 column G should = .05 cents per month only because it was not brought in with in the date range specified to apply the .15 cents so now the customer will only be charged just the .05 cents per month.

- so the formula has to determine a couple of things...1 is there a flat fee or is it just based off of a monthly rate....if there is a flat fee what is the date range's and then it also needs to be able to pro rate whatever value is typed in cell D4

that is it for the DP charges column G...

Now Column "H" on the main Page

-basically Column H will be based on the sheet listed as Spreadvalues...

-Column H on the Main Page is based off of the dates typed in Column E & F, and then it needs to search the sheet listed as "SpreadValues"

-These Dates in Columns B through E will change every year....so no matter what dates are typed in here it still goes back to the main page and the dates that were typed in...but will represent a Spread Value column F...

Ex.) basically the dates that are typed in on the main page, column E & F. should fall with in one of the rows listed on the Spreadvalues sheet. The date that was typed in for when the "product was brought in" should fall with in the date range listed in column B & C on the Spread Values sheet and then you should be able to follow that line item across and match it up with the Date range listed for the "Date product was sold" and what ever spread value is in that row should be was gets put into Column H on the Main page...That is it

-I hope all this makes sense...Thanks for any assistance you can give me

 

[Rijnsent]

Hey ellow,
would you happen to have a link to a (dropbox/google drive/etc) dummy file with your attempt so far?
Thanks,
Koen

 

[ellow]

Sorry to just now get back with you here is the link https://www.dropbox.com/s/htdxfp0rykjpgtw/delayed price calulation.xlsx?dl=0

 

[Rijnsent]

Hi Brad,
I have had a go at it, think I cracked the nut, but it's quite a nasty bunch of formulas I had to come up with... Essential IMHO is to keep quite some extra columns with calculations (you can hide them), because the whole calculation will be untraceable if you squeeze everything in one cell. I hope I made the steps small enough to make them understandable. For column G I added some named ranges to make the formula more readable.
https://www.dropbox.com/sh/l7ywfwzfk5j20sr/AACXwUGywU-4J-IWwdZY58Ppa?dl=0
Your file is named Delayed Price Calulation_formulas.xlsx
Cheers,
Koen

 

[ellow]

Koen,

Thank you so much for getting back to me, you made me feel better when you said this wasn't a easy one....Every time I started looking at this, I kept thinking I should be able to figure this out....But anyways, I am still somewhat confused. I see where you added the component's on the right of the main paige...and when I click on the cells, I see you put formulas in them, so at this point what do you want me to do? What I was hoping to have done is be able to manually type in the dates in columns E & F on the main paige and then have columns G & H automatically populate with the formulas based off the date ranges entered in Columns E & F....Thanks for all your time

 

[Rijnsent]

Hi ellow,
I don't understand your answer to be honoust... You have used Excel before? The sheet I created should help you getting the formulas in your own spreadsheet in place, drag them to the right cells and they do exactly as you described in your original question.
Ciao,
Koen

 

[ellow]

Yep...I am sorry. I must have not had my head on straight. We are all good now....Thank You so much for all your help, everything works perfect! That was a bear, and I appreciate all the time you put into it.
Thanks

 

[ellow]

Hey Koen,

One more question, I am trying to take this 1 step further. I need a formula that reads something like this...I want to find the absolute Value between Column L & Column C, then take that # and either add it to the exisisting # in column J or subtract it, depending if the # in column J is a positive or negative. and I am looking to put this equation in column M...sorry about all the confusion, they wanted to add one more step to the complicated formulas you did earlier..
Thanks

If Column J is a positive #, then subtract the Absolute Value between L & C, If Column J is a Negative # then add the Absolute Value between columns L & C #

 

[Rijnsent]

Hi ellow,

that would be something like =J2+(IF(J2<0;+1;-1)*ABS(L2-C2))

Cheers,

Koen