Hi Community members, 

I need help in finding correct formula for variance. I am an internal auditor in a company where I need to reconcile credit cards transaction for the whole business day. 

I have 4 cells in an excel sheet which looks like below:

1st cell: External service merchant sending daily transactions record

2nd cell: Transactions processed and recorded in the venue

3rd cell: Transactions taken through manual terminal

4th cell: Manual transactions manually recorded in venue’s software

For example, say at a certain day, venue’s cc payments received were 10,000$ out of which $9500 were through normal terminal, and 500$ were through manual terminal. Now when I will receive data for the day through external merchant, it will be 9500 and 500 to be recorded manually through our’s end, so, the data in the cell should look like this: 

1st cell: 9500

2nd cell: 9500

3rd cell: 500

4th cell: 500

Now, can anyone pls help me suggesting the formula where, if there is any variance (say any previous refunds recorded current day which then exceeds current day manual transactions) a formula that gives me correct variance when put the data?

Much appreciated.

​Hi Community members,  I need help in finding correct formula for variance. I am an internal auditor in a company where I need to reconcile credit cards transaction for the whole business day. I have 4 cells in an excel sheet which looks like below:1st cell: External service merchant sending daily transactions record2nd cell: Transactions processed and recorded in the venue3rd cell: Transactions taken through manual terminal4th cell: Manual transactions manually recorded in venue’s software For example, say at a certain day, venue’s cc payments received were 10,000$ out of which $9500 were through normal terminal, and 500$ were through manual terminal. Now when I will receive data for the day through external merchant, it will be 9500 and 500 to be recorded manually through our’s end, so, the data in the cell should look like this: 1st cell: 95002nd cell: 95003rd cell: 5004th cell: 500 Now, can anyone pls help me suggesting the formula where, if there is any variance (say any previous refunds recorded current day which then exceeds current day manual transactions) a formula that gives me correct variance when put the data? Much appreciated.  Read More

​