I have built a system tracks medications and supplements used in my household, and lets me know when to refill and how much I need. I’m looking for an easy way to make corrections when the calculated amount leftover doesn’t match reality during the check.
It’s essentially an inventory system that uses preset consumption amounts to prompt me when to replenish the supply. When I check the supply each month, I find that occasionally the real consumption amount is different than predicted, and I need to adjust the number for the amount leftover before replenishment. However, that amount is calculated so I can’t adjust it manually.
Each row includes the following columns (among others):
A. Name of Pill (Text)
B. Amount in container (#)
C. Number of loose pills on hand at start of month (#)
D. Number of containers on hand at start of month (#)
E. Total number of pills on hand at start of month (#ƒ C+D)
F. Amount used monthly (#ƒ calculated from other columns)
G. Total number of pills leftover at end of month (#ƒ E–F)
- I take 2 multivitamins a day, and the bottle contains 100 pills. At the end of the month (30 days) I should have 40 left (100-(2*30)), however, I forgot to take it a a few times so I actually have 45 left.
- My cat takes 1 calming pill per day, but I took her to the vet a couple times and used some extra ones. The bottle contains 90 pills for 90-day supply. When I tally up at the end of the second month, I see I have 25 left instead of 30.
In the first case I need to add to the ending total, and in the second I need to subtract.
What I’m doing now is adjusting the one number that can be manually entered (column C) which requires me to stop my inventory and do some math – not my strong suit.
What I want:
Ideally I would hit a button, enter the true amount leftover, and have a formula figure out the difference and adjust it.
How would you do this within a row?
Thanks in advance,