Calculating Compliance

[jmoricone]

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
)

 

[jmoricone]

Hello CJ_22 or anyone else who may help

I have updated my excel tracker and need assistance with Column U and W. The formulas do not seem to work. I though it did but when I decided to test the formula be adding a "Change" in Row 4, the formula did not work. I have attached a link to the spreadsheet and have highlighted the cells in red. Below is what what the formula should do if a change is added.


https://mega.nz/#!gx8kVRxR!UbVRHrihFDQqcmvKbJo3WktEc7ggBPAwpQUMlsTxYCQ


Row 4 in Column I4 I added Feb 1, 2017 and in Column J4 I added 3.0 Therefore the formula should be:
- Jan 25-Feb 01 = [7 days * (3.5*2)] = 49
- Feb 02-09 = [8 days * (3.0*2)] = 48

= (49 + 48 = 97) / 100 = .97 (rounded would then be 1.00).

Keeping the amount used V4 constant then W4 should be V4 (1.50) / U4 (1.0) = 150% since it should look at the Revised amount used (U4) due to the change. It appears the formula does this but the mathematical equations is not being calculated correctly.
(Original formula was V4 (1.50) / H4 (1.25) = 120%)

Thanks for the help