Excel question

setishock

Wizard of Wires
Messages
10,726
Location
4321
I have a template I made up to do my daily paperwork. Two of the items included in the template are the credit card total as seen from the front desk computer and the other is the batch out total from the credit card machine itself.

In the template I'd like to show if the computer is more than CC machine or the other way around. Then show the difference in another cell and indicate which is off.

Any Excel gurus out there?
 
It is a simple matter to subtract the the CC total from the computer total. If the result is 0, you are good. If the result is a positive number then the CC total is less than the computer. If the result is a negative number then the CC total is greater than the computer. You could use conditional formatting to change the color of the result cell to be different whether 0, positive or negative.

However, based on the info you provided, if they are different, there's no way to know which is correct, only that they are different.
 
I used a <> to compare the two figures going in both directions.
First to compare the computer figures to the batch out from the C/C. Then from the C/C batch to the computer figures.
That way it doesn't matter witch entry you make first, it still compares the two.

This is the formula I used. It's modified from a Microsoft demo page.
=IF(OR(C32<>C33, C33<>C32), "Out of Balance, Check Figures", "Balanced")
If the figures match, it returns a "true" and "Balanced" is shown in the cell under the totals. If the figures don't match, then it returns a "false" and "Out of Balance, Check Figures" is shown.

While it doesn't show which one or how much it's off, it does alert the person entering the daily figures that something is wrong and they need to go back and check the figures.

The rest of the sheet is finished, if I'd stop tinkering with it and adding more to it. I'm just wanting to have some built in checks and balances that are not obvious except to those that know what they're looking at. Too many managers have come through our properties that have had their hands in the till. I want to trip them up by having the spread sheet rat them out and them not know it.
 
Back
Top Bottom