Formula Assistance

[Ned93955]

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??

Thank you in advance.

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>

 

[Ned93955]

Any assistance I can get will be so appreciated.

Thank you!

 

[Ned93955]

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>

 

[Akashwani]

Hi,

What do you expect to see in the Difference column?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Log 1		Log 2		TTL of L1 & L2	Finc'l Records		Difference	Difference	Difference
2	150	Coffee	5	Coffee	155	50	Coffee	105	-105	105
3	253	Gardner	10	Gardner	263	323	Gardener	-60	60	60
4	328	Co Mtg	28	Co Mtg	356	596	Co Mtg	-240	240	240
5
Sheet12

Ak

 

[Ned93955]

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.

 

[Akashwani]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	Log 1	*	Log 2	*	TTL of L1 & L2	Finc'l Records	*	Difference	Difference	Difference	Difference	*
2	150	Coffee	5	Coffee	155	50	Coffee	105	-105	105	105	*
3	253	Gardner	10	Gardner	263	323	Gardener	-60	60	60	-60	*
4	328	Co Mtg	28	Co Mtg	356	596	Co Mtg	-240	240	240	-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

 

[Ned93955]

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.

 

[Akashwani]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Log 1		Log 2		TTL of L1 & L2	Finc'l Records		Difference	Difference	Difference	Difference	Difference
2	150	Coffee	5	Coffee	155		Coffee	155	-155	155	155	0
3	253	Gardner	10	Gardner	263	323	Gardener	-60	60	60	-60	-60
4	328	Co Mtg	28	Co Mtg	356		Co Mtg	356	-356	356	356	0
5
Sheet12

Ak

 

[Ned93955]

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?

 

[Akashwani]

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