Calculating Compliance

jmoricone

New Member
Joined
Nov 17, 2017
Messages
7
Hello all, I am new to the forum and in need of some expert help with formulas. I can typically figure out a formula or find what I need by searching online. However, I am trying to do a very complex calculation and am running into issues or not finding the answer online. I know how to do the calculation manually on paper but I am looking for a quicker solution where after entering a few key data points (highlighted in yellow), excel provides me the answer (this is also to save time as I have a lot of data to re-analyze). I need to figure out the correct amount of product taken (% compliance). I have created compliance formulas in the past but I have never added the possibility of change in between 2 data points. I have attached a mock spreadsheet for anyone to check my formulas and for assistance with the following:
· Round up column H to quarter (1/4) bottle.
o Example: If formula calculates “expected” 1.13 bottles used round up to 1.25; 1.4 to 1.5; 1.6 to 1.75; etc.
· Calculating % Compliance (column W) by the “rounded” value
o (.75 / 1.25) * 100% = 60% and not the actual value (.75 / 1.13) * 100% = 66%
o I need the rounded value (column H) as the amount returned is calculated by quarter (1/4) percent. If Excel cannot do this, then I am okay w/out a formula in column W, as I can leave that column blank and manually calculate the value.
· Calculating “Revised Expected Usage” (column U)
o This is where I am having the most difficulty as I cannot figure out how the individual changes in Row 5 / Columns H-N gets incorporated to change the value used from Column H to Column U.
o I believe this would be an If/Then statement, which I have little experience in Excel. Also, I believe that even if there are no changes, I would still need a formula in Column U where the value would equal (=) H until data is entered in between I to T.

Thank you
John
:eek:)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi, jmoricone!

No attachment shows in your post.
Maybe you find some free data transfer website, like wetransfer, and post a link here to download your mockup sheet.
 
Upvote 0
Hi, jmoricone!

No attachment shows in your post.
Maybe you find some free data transfer website, like wetransfer, and post a link here to download your mockup sheet.


Sorry I could not figure out how to attach directly into the post

Here is a link
[FONT=source_sans_proregular]https://mega.nz/#!poUiWKQZ!6A4eHgurTSy3nAaKvQWFJbm3_Hu9dm9hf4oy1oeoZys[/FONT]
 
Upvote 0
Need more clarification on what you are trying to accomplish in column U, but see if this solves your other issues.

https://we.tl/UChhtwdF6f


Yes, the rounding is what I was looking for.

Now I hope this explains it better

so for row 4 H we use the time between E4 & E5 * G4 (taken 2x)

however, now for row 5 we cannot used E5 & E6 as there is a change so we need to know how much of G5 is taken between E5 and I5 then add that to amount of J5 is taken between I5 and K5 (first change) and then add that to amount of L5 is taken between K5 and M5 (second change) and then add that to the amount of N5 is taken between M5 and E6 (third change)
 
Upvote 0
I think this is what you are after but I am not positive.

https://we.tl/aYRGbQQnxZ


Okay so i went through the calculation and came up with 5.08 (rounded would be 5.25) Here is my work:

Feb 09-15 = [6 days * (3.3*2)] = 39.6
Feb 15-18 = [3 days * (2.5*2)] = 15
Feb 18-21 = [3 days * (3.0*2)] = 18
Feb 21 to Apr 24 = [66 days * (3.3*2)] = 435.6

(39.6+15+18+435.6) / 100 = 5.08
 
Upvote 0
Are you asking a question? Or is everything working?


Hello,
Sorry, I was unclear. The formula did not work and I tried to review your formula but I am uncertain on what "LEN" means so I could not try and adjust the formula. Therefore, I typed up the manual calculation so that you may follow the logic.

Hope I am clearer here.

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top