[RESOLVED] Force Excel to calculate

[MartinLiss]

I have the following calculation in each cell of column G in my spreadsheet.

=IF(G53=0,G52, IF(F53>G53,F53,G53))

Each row in the sheet represents the data for the current month compared with the data in the previous month, so this formula is comparing the data in row 54 against the data in row 53. Column G represents a "high water mark" and the formula looks to see if the data in column F is ever higher than the data in column G and if it is then column G takes on that higher value.

That all works fine once I have data in a row, but at the start of each month I start a new row by manually adding data, left to right, in columns A through F, and the problem is that when I do that column G remains blank even though the formula is already in place in column G. The problem resolves itself, that is a value is calculated in column G, if I copy-paste-special (Formulas) from the previous row into the new row.

Is there some way for me to force Excel to calculate the value in column G of the new row without doing that?

[Siddharth Rout]

Martin seems like your excel is set to manual calculation... Click on tool->options. under the window 'option' select the tab "calculation" if it is not set to automatic, then set it. to manually force the calculation press F9 or press "calc now" or "calc sheet". See picture

[MartinLiss]

Thanks, it is set to Automatic which is why I don't understand why it doesn't calculate as soon as there is something in column F. If nobody has any better ideas I'll use F9.

[Siddharth Rout]

Martin if it's not confidential then can i see your workbook?

Edit:

Also I just had a thought... Are your those cells formated as "Text" if yes then this will happen. Change them to "General". Now it will autocalculate...

[MartinLiss]

Okay I've attached a cut-down spreadsheet. To reproduce the error(?) put the following data into the indicated cells, left to right.

A3: 3/2009
B3: 0
C3: 0
D3: 1125.76

[Siddharth Rout]

Okay two things...

I have the following calculation in each cell of column G in my spreadsheet.

=IF(G53=0,G52, IF(F53>G53,F53,G53))

I had a doubt but then I had to confirm it and I was right...

You are using circular referece in your worksheet. When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference. Microsoft Excel cannot automatically calculate all open workbooks when one of them contains a circular reference. You can remove a circular reference, or you can have Excel calculate each cell involved in the circular reference once by using the results of the previous iteration. Unless you change the default settings for iteration, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first.

Now In the picture that I showed above, there is a section which say Iteration, Change it to 101 from 1 in your worksheet to reproduce the error.

Now change it back to as shown in the pic below and it will work fine...

However I would suggest you not to work with Circular ref. This is an easy alternative. Put this in G3 and then copy it down...

=IF(OR(G2="",F3=""), "",IF(F3>G2,F3,G2))

Hope this helps...

[MartinLiss]

Thanks but there are a couple of problems.

Using my formula when I change the number of iterations to 101 I don't get the circular reference error.

The formula you supplied isn't quite what I need. I use this spreadsheet to track my Poker winnings and I create a new row at the start of every month and your formula works great for that. However during the month of March for example I would routinely change the value in D3 (which I suppose could be better named "Current Balance" instead of "Ending Balance") and if that value is increased each time (which means that I'm constantly winning - yay!) the value in G3 is increased as well. That's what I want but if I lose some money and decrease the value in G3, your formula decreases the value in G3 and I don't want that since G3 is meant to reflect the highest value I've ever had (the "high water" mark) and so it should never decrease.

[Siddharth Rout]

Do you mean to say that you want to display the maximum win that you ever had? then is this what you want?

Put this formula in cell G2 and pull it down "=MAX($F$2:F3)"

Hope I have got it correctly this time... Check the worksheet added

And yes you are winning in that sheet except one day where you lost

[MartinLiss]

Sorry, that's still not it. Let me explain by example. There are only 3 columns that affect the problem and they are:
D (Ending Balance) = How much I've won during the current month.
F (Overall Win/Loss) = How much I've won since I started playing.
G (High Water Mark) = The high point of my winnings.

So let's say that on 3/9 the totals were

D= 2000
F = 2214.15
G = 2214.15

Today (3/10) I win $20, so

D= 2020
F = 2234.15
G = 2234.15

On 3/11 I win $40, so

D= 2060
F = 2274.15
G = 2274.15

However on 3/12 I lose $30, so

D= 2030
F = 2244.15
G = 2274.15

It's important to note, and I don't think I mentioned this before, that except when I create a new row at the start of each month that column D is the only one I change manually

[Siddharth Rout]

But Martin that's what the formula in my previous post will give you? see pic

[MartinLiss]

In the spreadsheet that you attached in post #8, if I change D3 from 2000 to 1900, G3 changes from 2214.15 to 2114.15. The value in G3 should never decrease.

[Siddharth Rout]

ok let's start all over again... I am sure I'll get this sorted

Give me three examples but this time give 3 examples which include all cells from B to G.

[MartinLiss]

I sent a document to your email address.

[Siddharth Rout]

I have replied...

[MartinLiss]

Sorry but in the new sheet that you sent me if I change D9 from 1200 to 1100, G9 is decreased from 4500 to 4400 and it should never decrease.

[Siddharth Rout]

Ah! I understand now

So you can change a particular cell anytime during that month. I was under the impression that it will change only once and that too at the end of the month....

Give me a moment...

[MartinLiss]

You're correct. I even sometimes update it more than once in the same day.

[Siddharth Rout]

Sent a mail again...

[MartinLiss]

I was able to get into the VBA code and make the change I needed to make. Thank you very much for your help.