Need a formula, product being delivered for a period of time

[ellow]

Good Morning I am new to this website so I hope I am doing this right...I am looking for a formula that falls with in these guide lines....Our Rates are as follows from 9/1/14 through 12/31/14 it is a flat .15 cents and then .05 cents per month there after. The .15 cents will never be prorated but the .05 per month is prorated.
(Ex.) John brings us his product on 11/2/14 and decides to pick it up on 3/15/15 he will automatically be charged .15 cents till the end of the year, then 5 cents per month there after...that 5 cents gets prorated by the day for that month...so if his product is only up here for 15 days he would only get charged 2 1/2 cents, I would like a formula that allows me to enter the date the product was brought in and enter a date when the product was picked up, and it will spit out how much they accured in charges....I hope this makes sence....Thanks for any help you can provide

 

[par60056]

on a separate sheet called "SpreadValues" I entered the following table:

spreadname	startdate	enddate	spread value
	10/1/2014	11/30/2014	0
	12/1/2014	2/28/2014	0.1325
	3/1/2015	4/30/2015	0.08
	5/1/2015	6/30/2015	0.04
	7/1/2015	8/31/2015

<tbody>
</tbody>

Then I put this formula in H13:

=IF(MATCH(E13,SpreadValues!B2:B5,1)=(MATCH(F13,SpreadValues!C2:C5,1)+1),0,SUM(INDIRECT("SpreadValues!D"&(MATCH(E13,SpreadValues!B1:B5,1)+1)):INDIRECT("SpreadValues!D"&(MATCH(F13,SpreadValues!C1:C5,1)+1))))

Turning it red and green is fairly easy using conditional formatting. I'll leave that up to you.

 

[ellow]

Mr. Excel...I think we are onto it now....Thank you for all your help and patience with me, It appears that column G, cell G13 and below all works, and then I go to put that formula into cell H13 and I get the dreaded "#N/A" A value is not available to the formula or function. It appears that all the values are lining up with the cells that you told me, so I am not sure what is gone on...I went to sheet #2 and entered the spreads just like you have entered above, is there anything I need to do to make Sheet #1 Cell G13 read Sheet #2...I am not real good at excel, But I am learning slowly...again thanks for your patience

 

[par60056]

The formula that I gave you for H13 assumes that you move or copy the Spread Values from the top of the sheet to a separate sheet AND name that sheet "SpreadValues" The SpreadValues sheet should look like the table in Post #11 above.

You can leave them on the same sheet but as the SpreadValues table grows over time you will run into the rest of your data. If you want to keep them on the same page you will need to make some adjustments to the formula to remove the reference to the SpreadValues sheet and change the range in the Match call to the columns you have the data in.

 

[ellow]

I have sheet #2 renamed as "SpreadValues" I have my table set up exactly like you do...I plug in the formula into cell H13...The answer comes back at 0.0400
The sample that I have plugged in is
E13= 3/1/2015
F13= 5/31/2015

Cell G13= .15 in DP charges which is correct

Spread Values
Cell B4 = 3/1/15 which indicates a spread value of .08 cents...But what if this was 4/15/15, how does it know what date range to use??
Cell C? = What spread value does my "Date Product was sold" represent..I was thinking that is why I need two separate date ranges?? I am confused on this part.

Cell H13 answer = .04 cents (according to the formula) and I have everything brought out to 4 decimal points...The actual answer should be ...
Spread Value = .12 cents (Cell D4 on the Spread Value Sheet = 8 cents & Cell D5 on the spread Value sheet = .04 cents)

Basis
Cell C13= -.20
Cell D13= .07

Total Basis Difference= .27 Cents + .12 cents in spread Value = .39 cents.... (-) G13 - .15 cents in DP Charges = .24 cents difference...Now the formula did come up with .04 cents which I think cause it wasn't taken in to consideration that the basis on the Date product was brought in was showing up as a minus (-) .20 so I changed it to show as a +.20 and the formula never changed, and then I try to drag that formula down and all the other cells come up as #N/A so I am not sure what I am doing wrong...Sorry

 

[par60056]

The #N/A is my fault. When you drag the formula down, it will change the range of cells being looked at on SpeadValues. You don't want that. So you need to put $ in the range so it does not change it. As for the calculation I was not sure if the starting spread value was added.

The use of the MATCH function looks for the row of the highest value in the spread table that the date is less than. By searching column B for the drop off date and column C for the sold date you get the range of rows that you need to sum the spread value for.

Try this for H13. It should allow dragging down now and include the starting period spread value.

=IF(MATCH(E13,SpreadValues!$B$1:$B$5,1)=(MATCH(F13,SpreadValues!$C$1:$C$5,1)+1),0,SUM(INDIRECT("SpreadValues!D"&(MATCH(E13,SpreadValues!$B$1:$B$5,1))):INDIRECT("SpreadValues!D"&(MATCH(F13,SpreadValues!$C$1:$C$5,1)+1))))

Looking at the beginning of the IF statement:
MATCH(E13,SpreadValues!$B$1:$B$5,1)=(MATCH(F13,SpreadValues!$C$1:$C$5,1)+1

E13=3/1/2015
Searching column B using "MATCH(E13,SpreadValues!$B$1:$B$5,1)" returns row 4
F13=5/31/2015
Searching column C using "(MATCH(F13,SpreadValues!$C$1:$C$5,1)" returns 4 because 4/30/15 is the highest value < 5/31
But I always add 1 the the search for the end so this make the end row Row 5

So the sum adds D4:D5 from the SpreadValues sheet.

The way I wrote this, if both dates are in the same range then the answer is 0.
So if F13=4/15/2015 then H13 is 0

 

[ellow]

Ok I think we are getting close...

It's up to you, but I think we might need to go back to having 2 different Date Ranges...1) for "Date Product was delivered" and the other set of date ranges for "Date Product was sold" It works like this...

Sheet 3 I renamed to "SpreadValues" I have laid out like this


Row 1, B & C = Labeled "Brought In" Row 1, E & F = Labeled "Sold" Cell G1 = Spread Value

B2=10/1/2015 C2=11/30/2015 E2= 10/1/2015 F2= 11/30/2015 G2= 0.00
B3=12/1/2015 C3=2/28/2015 E3=12/1/2015 F3= 2/28/2015 G3= .1325
B4= 3/1/2015 C4=4/30/15 E4=3/1/2015 F4= 4/30/2015 G4= .08
B5= 5/1/2015 C5=6/30/15 E5=5/1/2015 F5= 6/30/2015 G5= .04
B6=7/1/2015 C6=8/31/2015 E6=7/1/2015 F6= 8/31/2015 G6= 0.00 for Now, I will type in a value later
B7= 9/1/2015 C7=11/30/2015 E7=9/1/2015 F7= 11/30/15 G7= 0.00 for Now, I will type in a value later
then these dates should continue on...again for the following year so on and so fourth

So Examples would be, if brought in between the date ranges of 9/1/14 - 11/30/14 and then sold during the same date range they would have a spread value of "0"
but if the producer would have brought us product in between the date ranges of 9/1/14 - 11/30/14 and sold it during the date range of 12/1/14 - 2/28/15 the spread value would be .1325

Ok now if the producer was to bring us product in between the date ranges of 9/1/14 - 11/30/14 and then sold it between the date range of 3/1/15 - 4/30/15 he would have a accumulative spread value of .1325 + .08 = .2125 cents...

If a producer brings us product in between the date ranges of 9/1/14 - 11/30/14 and then turns around and sells it in between the date ranges of 5/1/15 - 6/30/15 he will have accumulated a spread value of 0.00 + .1325 + .08 + .04 = .2525 cents.

If a producer bring us product in between the date ranges of 3/1/15 - 4/30/15 and sells it in between the date ranges of 5/1/15 - 6/30/15 he would have accumulated a spread value of .08 + .04 cents = .12 cents...

So column E "Date product was brought in", Cell E13 and below has to be based off B2:B7 and also C2:C7
Column F "Date Product was sold", Cell F13 and below has to be based off of E2:E7 and also F2:F7

The date range that the product was brought in should correspond with a Spread value if you follow it all the way across and the date range that the product was sold should correspond with a spread value if you follow that row across. Between the 2 date ranges you add up the all the spread values that correspond with the date range and the spread values in between...Thanks again

 

[par60056]

You had the second set of date in D&E now they are in E&F. What is in D?

The dates in Bx and Ex are the same. The dates in Cx and Fx are the same. The goal is to sum up the value in G from the row that the "Brought in" date to the row with the "sold" date

Did you try out the latest formula?

 

[ellow]

Yes I did try the latest formula....and yes in your last statement you stated it correctly....But when I enter dates some are correct and others are not....

Ex.

Product Delivered Product Sold Spread Value from Formula What the Spread Value Should be
3/1/15 5/31/15 .12 Correct
4/1/15 4/30/15 .12 There should be no Spread "0"
11/28/14 2/12/15 .1325 Correct
3/1/15 4/30/15 .12 Should be no spread


Ok...I think I know what going on, everything appears to be correct, other then when the product was brought in say in 3/1/15 and then sold on 4/30/15...there should be no spread ( I don't think I ever mentioned that, sorry)...The only time there should be a spread is if the "Date product was delivered" ROW exceeds the "Date Product was Sold" row in the "SpreadValues" sheet...Hopefully this makes sense, I think if we get this part figured out, then it should work.... Thanks

 

[par60056]

The problem it is having is if the end date is exactly the end date of a range. It is pushing out to the next row.

You can leave the spreadvalues table the way that you have it but you really only need to use the first column of dates in the table.

Try this format for Hx:
=IF(MATCH(E13,SpreadValues!$B$1:$B$5,1)=(MATCH(F13,SpreadValues!$B$1:$B$5,1)),0,SUM(INDIRECT("SpreadValues!D"&(MATCH(E13,SpreadValues!$B$1:$B$5,1))):INDIRECT("SpreadValues!D"&(MATCH(F13,SpreadValues!$B$1:$B$5,1)))))

as you give better information and better predicted results it is easier to get the formula to match.

 

[ellow]

MR. Excel...You got it!!!!!!! Unbelievable, this is a tremendous help to my job and what we have to do...I can't thank you enough for taking the time and not giving up, even know I am sure you wanted to.

Thank you so much!!!!

1 more question...

So now In column I, I want to write a formula that takes into consideration Basis at delivery (+) plus what the basis was at the time the product was sold (+) plus the accumulated spread value (-) the DP charges

So right now it looks like this...
Basis @ delivery, Cell C13 = -.20
Basis On sale Date, Cell D13 = +.03
Accumulated Spread Value, Cell H13 = .25 (Which is the formula that you just did)
DP Charges that occurred, Cell G13 = .43 (Which is the formula that you came up with)

So this would = -.35...which is actually NOT correct.

I am not sure how to word this, but what I am trying to say is....That we actually lost out on a total of .23 cents ( the -.20 should actually be like a +.20 and then +.03 cents +.25 cents and then - the Dp charges of .43 cents)

Ok then on the other hand if a Producer brought us corn and we had a basis of plus (+).10 cents (Cell C14) and then turned around and sold it when our basis was plus (+) .12 cents (Cell D14) we actually only lost out on .02 cents, so now it would be .02 cents plus the spread value (Cell H14) minus (-) Dp charges (Cell G14)....

And another example would be if we had plus .10 basis value (Cell C15) when they bring the product to us and then had minus (-) .07 basis when they sold it (Cell D15), in this case the advantage goes to us and that should show a difference of .17 cents + the accumulated spread value (Cell H15) - the DP charges (Cell G15)