Convert Months to Days for Total price Calculation

Which calculation, I assume column N (Total Price)???

What dates do you use to get number of days?

In the raw data sheet of workbook where we worked on data discrepancies, you talked about a formula in column AH that you couldn't include because of circular references. What was that formula?

In the raw data sheet of workbook where we worked on data discrepancies, you talked about a formula in column AH that you couldn't include because of circular references. What was that formula?

That formula is in Col AG.  It takes the original Term Month / no of service items from the original Quote sheet.  I could not add col AG to the end of Col T without getting a circular reference so I had to copy and range value the formula in col AH to that it can be added.

https://filedb.experts-exchange.com/incoming/2020/01_w03/1441125/29169174a.xlsm

Note made a correction to my previous comment

The formula I'm asking for is the one missing in column AH.

The one indicated by the purple arrow.

There is no formula for col Ah this is just a copy of Col AG with the formula removed using the “copy past special” function.  In other words Col Ah is a duplicate of Col AG with no formulas to rid the circular reference from happening

Are you saying you copied AG41 and then PasteSpecial_->values to AH41?

Are you saying you copied AG41 and then PasteSpecial_->values to AH41?

Yes, that is correct

When I do that I get  (0.0000000)

Col-AH.docx

When I do that I get  (0.0000000)

See the embeded file.  Hopefully that example will be more clear.  You can test this by removing the +AH41 from the end of the formula in Col T for rows 41- 44  and it resets the values/differences.  Once this is done you will notice that column AG and AH values are the same.  You can  clear the content in col AH for those rows and apply the pastespecial value to AH41 through AH44 and repeat the add to col T.

I'm sorry I'm having difficulty with this but what am I doing wrong?

I guess I need to ask; should I expect to see anything other than zeros?
2020-01-30_06-46-53.mp4

I'm sorry I'm having difficulty with this but what am I doing wrong?

I'm not sure, but see if the video i attempted to create helps :).

You didn't attach it.

I could be wrong but it looks like what you set me was a copy of Adobe Premier Elements 2018 rather than your video. If that app has an 'Export' function then do that and attach the output here. Hopefully it will be in a format I can read.

Sorry about that.  Hope this works.
workbook-video.mp4

Yes thanks, I can play that but it's a little fast to follow. Can you describe, step by step, what you did? In other words...

1. Remove +AH41 from the formula
2. Copy ? to ?
3. Etc

Step by step instructions
1. remove the content in column AH41 through AH44, then
2. select cell T41 and remove the +AH41 from the end of formula and click <Enter>, then
3. select cell T41 and copy formula from T41 to T44. (This resets the results in column AG41 to AG44 from (0.0000000) to  0.0124194), then
3. select cell AG41 through AG44 and select<copy>, then
4. select cell AH41,  <right click> and select <paste special> and under the Paste group select <values and number formats> and <click ok> to close, then
5. select T41 and add +AH41 to the end of the formula, then copy T41 to T44. (This avoids the circle reference and finds the term match if column AG41 through AG44 is now set to (0.0000000)

Okay, that works. Now I just need to figure out how to do that in code:)

In the attached workbook I've done this:

• Use days rather than months in price calculation
• Deleted/replaced old columns T, U, Y and Z with new columns W and X,
• Swapped the Comment and Key columns

The net effect of this is that the Raw_Data Total Price matches the Original Quotes Total Price but the SI total prices are slightly different. The reason for that is that Original Term as a number of months is vague in that it can have a variable number of 28 29, 30 or 31 days. For example the first SI in quote Q-11223344 has an Original Term of 7 and in your method described above you go through a lot to compensate for that but I don't believe it's quite accurate. What I did instead was to use the number of days between the start dates and the end dates (inclusive). Looking at the first SI for that quote in Original Quotes and Raw_Data, the days in the former is 213 and the latter 133 and the $1038.09 total price in the Raw_Data was derived by multiplying $1,662.50 times the ratio of the number of days (133/213).

While I probably can be convinced otherwise, I frankly don’t see how what I did can be incorrect.
29170885.xlsm

Thanks Martin!  I haven't had a chance to look a the workbook yet.  As long as the total month breakdown cost is reason within days calculation you came up with and the bottom line total for each item matches the quote, we should hopefully be good to go on this task anyway.

In testing the workbook,  I was not able to add the quote as I received a run time error on the Renewal date.  I entered a start date of 11/12/2019 and end date of 03/14/21 and a renewal date of 03/13/21 since the validation message says that the Renewal date can not be after the end date.

Error code on

 If strParts(0) > 0 Or strParts(1) > 2 Or (strParts(1) = "2" And strParts(2) > 0) Then

Not sure how the Renewal date code is working.

I'm embarrassed about this but a while back you made a similar comment about the renewal date and my response was that there can only be a renewal date if the term is more than 12. While that's true it totally ignored the fact that the code that handled the renewal date has been wrong for a long time. I'm fixing that now and I'm a little stuck at the point where the difference between the renewal date and the end date is being looked at and depending on what those dates are,  a "renewal date must be within the period two months prior to the end date and the end date inclusive" error message can show up. As we know, dealing with months is difficult. Do you need that validation or would something requiring renewal date to be before the end date be enough?

There were quite a lot of things wrong with the renewal date process, but I think I've fixed them all. You'll also find that I've changed the calendar form a bit. There's a lot more I could do with it if we have the time.
29170885a.xlsm

Thanks Martin,

I will take a look at it.  I most certainly have the time to get things working correctly.

I entered Q-88888888 and had no issues with the Original Quote sheet.  However, the Raw_Data sheet dates are incorrect as well as the line item totals.  See attachment for more details
29170885a.xlsm

I don't agree with the the individual totals in the breakdown, but the grand total matches the Original Quote data.
29170885b.xlsm

Yes, I understand it is a bit confusing.  I do know that within the quote it's noted that the monthly price on the original quote is rounded to two decimal places for display purposes only, but  could have as many as up to eight decimal places in the monthly price.  However,  the totals are calculated using actual price that's not showing due to the rounding, which is why we are seeing the differences and also, why we are having to compensate for the difference.

We have tried to get them to correct this but they have seem very reluctant to do so.

The 29170885b.xlsm workbook I attached does things by days and not months. In the question where you asked to correct the discrepancy between Original Quotes and RAW_DATA you accepted my answer which used days, even though there were small differences in SI total prices. In that question the total prices were the same. Are you suggesting that that is no longer acceptable?

No, that’s not what I am trying to say.  

In a previous post within this thread.  I basically stated that as long as it matches back to the original term for each line item let’s go with using number of days,

Keep in mind that there has to be a period of performance for the subscription term based on a start and end date.  So know matter if you use no of days the year and total price still need to be broken down for each line item correctly  as it has to fall within a annual accounting cycle (Jan.-dec ).

My formula accomplished finding the rounding difference after the breakdown.  It was a matter of converting this to a script which I have no knowledge in doing.  So, I thought if using your suggestion of days would accomplish this I was for it.

The goal is not for you to  do unnecessary work or rework

I apologize for lack of  understanding on part

Thank you Martin!

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018

I've corrected a problem. See quote Q-99999999.
29170885c.xlsm

I tested the work book C by adding another Q-10101010 and still having the same issue.  This time both sheets are showing discrepancy within the line item and totals.  See attachment
29170885c_-Test-result.xlsm

I stand by the total on the OQ sheet. Here I've formatted it to show 4 decimal places.
I will work on the breakdown if you agree with my amounts which are based on days, not months.

The formula for total breaks down to M x S x W x X which is

1 x 16.33 x 496 x 0.29034274 = 2,351.68328... or 2,351.633 when rounded. Note that you don't see "0.29034274" until you format N33 to 8 decimals.

I understand and I am not disagreeing with the math.  Unfortunately the difference no matter how small needs to match up with the Quote.

So if the math is calculating correctly but due to some sort of rounding convention used by the vendor we have to manipulate our internal workbook to match their total result output.  Again, hence the reason for the formula I presented to find the difference and add it on to the end of the formula so it matches for each line item and total.

Apparently the days calculation will not find this discrepancy and if that is not the case then we have reset back to before the changes and I am going to have to cleanup my formula a bit and used that instead.  

There is no getting around the quote and work book needing to have an exact match.

Okay then I'm very sorry to say that I can't help you any more with this project. I suggest you go back to some workbook that worked with months and ask a new question. When you do I'll post the change in the code that made Q-99999999 work but that's as far as I can go.

Ok, you may not can find the solution to getting the dollars to match.  I can just figure out the difference and apply to the formula that was there before, but I still need your help with converted everything to table format for both Original Quote and Raw_Data sheets so that I am able to utilize power BI and also we never finalized the email notifications for the renewals.

I still need your help with converted everything to table format for both Original Quote and Raw_Data sheets

If you start a new question based on a workbook that uses months then I can probably help you with that.

we never finalized the email notifications for the renewals

Go to your list of questions. All of them are closed.