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
)
· 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
)
