# Formula Assistance

#### Ned93955

##### New Member
I am working on a simple spreadsheet and would like to know if the answers are correct (the formula that I am most concerned with is the formula in the "difference" column). Log 1 and Log 2 are two petty cash accounts and when the bookkeeper pays a vendor from petty cash, it is entered into the finc'l records. So looking at the spreadsheet below, it appears that \$155 was taken from petty cash and only \$50 was paid towards coffee. This would mean that \$105 is missing. On the other hand, it looks like two vendors were overpaid for Gardening service and Co Mtg. I want to make sure this spreadsheet is correct. Does this look accurate??

 Log 1 Log 2 TTL of L1 & L2 Finc'l Records Difference \$150 Coffee \$5 Coffee \$155 \$50 Coffee -\$105 \$253 Gardner \$10 Gardner \$263 \$323 Gardener \$60 \$328 Co Mtg \$28 Co Mtg \$356 \$596 Co Mtg \$240

<tbody>
</tbody>

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Any assistance I can get will be so appreciated.

Thank you!

I realized I did not put in the formulas that are currently in place.

 Log 1 Log 2 TTL of L1 & L2 Finc'l Records Difference \$150 Coffee \$5 Coffee \$155 \$50 Coffee -\$105<-- this formula is L1 & L2 column - Difference \$253 Gardner \$10 Gardner \$263 \$323 Gardener \$60 \$328 Co Mtg \$28 Co Mtg \$356 \$596 Co Mtg \$240

<tbody>
</tbody>

Hi,

What do you expect to see in the Difference column?

Excel Workbook
ABCDEFGHIJK
1Log 1Log 2TTL of L1 & L2Finc'l RecordsDifferenceDifferenceDifference
2150Coffee5Coffee15550Coffee105-105105
3253Gardner10Gardner263323Gardener-606060
4328Co Mtg28Co Mtg356596Co Mtg-240240240
5
Sheet12

Ak

That is great!! Thank you for the reply. One other question: So does this mean the the original formula I had in there was going the wrong way, for I2, I had E2-F2? I basically want to see if any money is missing and if we overpaid a vendor. Would it be best to use your formula of J2=IF(F2>E2,F2-E2,E2-F2)? Will it give me a negative and positive number?

I'm sorry, I do not know how to place the picture of the spreadsheet as you did you your example.

Hi,

I don't have the answer/solution to your question, I was posting example formulas in the hope that one of them would be correct, that's why I asked if you could post your expected results. I have added another formula...........

Excel Workbook
ABCDEFGHIJKL
1Log 1*Log 2*TTL of L1 & L2Finc'l Records*DifferenceDifferenceDifferenceDifference*
2150Coffee5Coffee15550Coffee105-105105105*
3253Gardner10Gardner263323Gardener-606060-60*
4328Co Mtg28Co Mtg356596Co Mtg-240240240-240*
5************
Sheet12

To post sample data take a look here at post #2.......

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

Ak

Thank you for the other formula and for replying.

Basically, the difference should always be zero. That is the expected answer. However, if funds are missing or there was an overpayment, I should get either a negative or positive number. I wasn't sure which way the formula should go.

Thanks again.

Hi,

So does the formula in column K give you the expected result?
I guess a negative being an over payment.
What should the Difference be IF there is no value in column Finc'l Records?
I have added another formula in column L to reflect the above question............

Excel Workbook
ABCDEFGHIJKLM
1Log 1Log 2TTL of L1 & L2Finc'l RecordsDifferenceDifferenceDifferenceDifferenceDifference
2150Coffee5Coffee155Coffee155-1551551550
3253Gardner10Gardner263323Gardener-606060-60-60
4328Co Mtg28Co Mtg356Co Mtg356-3563563560
5
Sheet12

Ak

Yes, it appears that column K is the answer I was looking for. I will try the new formula too!

What if introduce a new log 3?

Hi,

If you are changing the layout of your data, you will obviously need a different formula.
This may well be beyond my (limited) ability, so I suggest that you start a new thread and post your sample data and expected results.

I'm sorry that I couldn't have been of more help to you.

Good luck.

Ak

Replies
3
Views
183
Replies
6
Views
197
Replies
3
Views
153
Replies
31
Views
4K
Replies
3
Views
1K

1,206,972
Messages
6,075,929
Members
446,171
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back