Excel Formula Null Value

Question

I am using Excel to create some formuals to calculate a total dollar ammount (profit).

At the moment there will be multiple rows that will calculate profit based on the amount of profit from the number above it.

So say that Day 1 there is $1000 and that make 5% profit. Day 2 there is $1005 and that make 3% profit, Day 3 etc. Compounding the money together. This will go on forever (or into the foreseeable future).

At the bottom I want to have a TOTAL PROFIT calculation. This will be all the profits from each line added together, (and minus the inital $1000)

On each line, the profit is calculated by taking the inital value (say $1005), times that by the % increase or decrease, then minus the $1005 from the previous value of $1000. Hence the profit for that day will come out. If I make the formula and have no values in the 2 fields that make up the calculation, it defaults to PROFIT = -$1000 (because each field is empty, and it is still taking away the previous days total).

That looks really complicated! Hope this makes some sense and thanks for any help.

Answer 1

If I understand your problem correctly, what you want to do is to calculate the profit if the previous entry is non-empty, and just output 0 if it is empty. If so, a simple Excel IF statement should suffice. The syntax:

IF(condition, true_statement, false_statement)

In your case, for cell B2 (with profit percentages in the A column) you could write

IF(B1 <> 0, A2*B1, 0)

Answer 2

It would probably be best if you provided an example, exactly what the input is and what the output should be. Let me try and give an example of what I think you meant:

• Cell A1 contains the initial sum: 1000
• Cells B1, B2, B3, B4 contain the profit each day, say 5, 3, 3, 1
• Cells B5, ..., B100 are empty, because these days haven't happened yet.
• Desired output: cell D1 should contain the total profit, which is in this case 125.08

So, what we'll do is calculate in A2, ..., A100 the amount of money at the beginning of that day:

• In cell A2, we will have =A1*(B1+100)/100

And copy these all the way down to A101. Therefore, A2 will show 1050 (5% profit on $1000), A3 will show 1081.5 (3% profit on $1050), etc. Since B5 and up are empty, this is treated as zero, so A6 and up will all be the same number, 1125.08. Then to calculate the total profit.

• In cell D1, put =A100-A$1