"Investment Formula"

[Marwan69]

Dear All,

Good day/night.

I have a tabulate consisting of the following inputs:
Beg. Value(A1)
Purchases(B1)
Sales(C1)
End. Value(D1)
Formula(E1)=If(....

I'd like to create a formula to be able to divide the (Ending value/Beg. value) -1, but with slight adjustment. I'll explain:

Sometimes there's 0 value under one or two of the first three captions (Beg. Value, Purchases, or Sales) and i want my formula to capture that. Hope that's clear.

Many thanks,
Marwan

 

[AhoyNC]

Try:

=IF(PRODUCT($A$1:$C$1)=0,"Zero Value",(D1/A1)-1)

 

[Marwan69]

Dear AhoyNC,

Thanks for your help.

Actually I'm not trying to multiply these cells. These cells have values and I need to adjust my formula to accommodate the different scenarios of these values. Meaning:

If there's an amount say: $100 under the beginning value, $150 under purchases, (-$100) under sales. How would I build up my formula to capture all these inputs, taking that sometimes one or more of these values = 0.

I tries the following formula:

IF(OR(A1<>0,B1<>0,C1<>0),(D1/A1))-1,0)

But didn't work, because there's a case that there's not any value under A1 or B1, or both have 0, or some have values and some have 0!
It's a mess for me!

Hope I made it clear through these examples.

Many thanks,
Marwan

 

[AhoyNC]

By multiplying the cells if any one or more of them equals zero the IF statement will return zero.
You could also change the OR in your formula to AND.

Excel Workbook
	A	B	C	D	E	F	G
1	100	150	-100	125	0.25		0.25
2	100	0	-100	125	0		0
3	0	150	0	125	0		0
4	0	150	-100	125	0		0
Sheet

 

[joeu2004]

IF(OR(A1<>0,B1<>0,C1<>0),(D1/A1))-1,0)
But didn't work

Presumably it is okay to have zero purchases (B1) or zero sales (C1) or even both (!) in any perriod. The only problem is when the beginning balance (A1) is zero.

So you might do one of the following:

=IFERROR(D1/A1 - 1, 0)
or
=IF(OR(A1=0,A1=""), 0, D1/A1 - 1)
or
=IF(OR(A1={0,""}), 0, D1/A1 - 1)

formatted as Percentage.

Both formula also handle the condition when A1 appears to be blank, either because it is empty (no value) or because its value is the null string ("").

Those formulas make the simplifying assumption that neither the beginning balance (A1) nor the ending balance (D1) can be negative.

Is that a valid assumption?

 

[Marwan69]

Thanks AhoyNC,

The point with your formula sir is if there's not value in B or C, the formula will return 0! when in fact I still want the formula to divide: D/A.

Actually this is pertaining to investment performance, where I want excel to add the purchases(B) and subtract the sales(C) from the beginning and ending balances. I'll explain:

The formula in E cell should be as follows:

E: (D-(B+C))/(A+(B-C)).

The problem here is when I have 0 value under A the formula corrupts. On the other hand, if I have 0 value under B or C, I still want to divide D/A even if these cells (B,C) have 0 values.

Many thanks for your effort.

 

[Marwan69]

Dear Joeu2004,

Thanks for your reply and effort.

The formula =IF(OR(A1=0,A1=""), 0, D1/A1 - 1) still doesn't count the values in B & C, no does it count if these values where 0.

My ultimate formula should read as follows:

The formula in E cell should be as follows (Under E):

E: (D-(B+C))/(A+(B-C)).

Many thanks,
Marwan

 

[Marwan69]

Any update guys!

 

[shg]

If you would provide several examples that cover the cases of interest, someone could understand what you want.

 

[Marwan69]

[TABLE="width: 279"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]HiShg,





[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]3,663[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-11[/TD]
[TD="align: right"]3,652[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]3,718[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3,718[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]13,348[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]1,958[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20,306[/TD]
[TD="align: right"]1,958[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]1,923[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,923[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]5,053[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,395[/TD]
[TD="align: right"]342[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"]2,440[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,734[/TD]
[TD="align: right"]294[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"]2,406[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]409[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,815[/TD]
[TD="align: right"]409[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD="align: right"]5,038[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5,062[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD="align: right"]626[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-627[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD="align: right"]20,050[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]20,170[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD="align: right"]21,598[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31,676[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: right"]10,576[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10,576[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G4[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G5[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G6[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G7[/TD]
[TD]10,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G8[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G9[/TD]
[TD]3641[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G10[/TD]
[TD]3718[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to calculate G/C-1 provided that adjustments for Purchases & Sales should be reflected in the G&C. The issue is sometimes there isn't any value in the Purchases and/sales which will return the formula to #value error. And what makes it even worse is when there isn't value under the C as shown in row#20 above.

Hope is clear.

Many thanks,
Marwan