Excel Formula

  TonyV 20:06 15 Feb 2007

I have a simple spread sheet that deals with some small investments and ends up showing me the % loss or gain.

There are three columns that act as a feed to a graph which shows the growth/loss curve. Col 1 = Date, Col 2 = Total Value and Col 3 = % of the original investment.

My query is this, when I fill in the date and the Total Value, the % figure comes up automatically in Col 3. There doesn't appear to be a formula in the "new" % cell. Yet there is when I click return after inserting the Total Value. Can anyone tell me how this happens? I must have done something way back when, but it escapes me at the moment. I have extended the sheet to accommodate some more figures and this time I cannot get the % figure to come up automatically.



  Taff™ 08:33 16 Feb 2007

I suspect the cells use an IF function something like a formula reading =IF(A2,A2,"") - if you click in one of these cells can you see it? I seem to remember that the formulas can be hidden - where are you VoG™?

You may be able to drag the formulas down to your new rows, otherwise I would suggest copying the last 3 rows that work and then "Insert Copied Cells" and alter the dates and figures.

  Simsy 08:56 16 Feb 2007

"There doesn't appear to be a formula"... I'm assuming that you are deducing that as no result is appearing in the cell?

You then say...

"Yet there is when I click return after inserting the Total Value"...

Now maybe I'm missing something here, but that seems right to me, because...

until you press "Return", as described above, you haven't finished entering the figure in the Total Valu cell... therefore the value, (Until you press return), is nil, hence the % is nil.

Does that help, or have I misunderstood the problem?



  xania 08:56 16 Feb 2007

I have a similar problem (Excel 2000) in that sometimes when I enter a formula incorrectly, once I correct it, rather than ther esult appearing in the cell, the formula is printed. I get over this by deleting the 'damaged' cell or column and then re-entering the formula. Works every time.

One thing has just occurred to me, however. Perhaps the formula has somehow been formated as text. Just in case, try deleing the '=' and then re-typing it.

  TonyV 10:07 16 Feb 2007

No, the % cell has no formula in but when I put the Value in the Value cell then click return, the % cell then has its' figure in the % cell. Yet there is no formula in the % cell prior to entering the value and clicking Return. There is no "Hidden" formatting in the cells.

What I mean is as I have described above. There is no formula in the % cell until such time as you enter the Value in its' cell and then click return. Then the % figure comes up in the % cell, but there was no formula in the % cell to start with! It's a nice feature to have but how I got it to do it I really have no idea now. The workbook has been in existence for a goodly number of years now and time obviously dulls the memory!

Mine is Excel 2000. But again, there is no formula in the % cell. So how does it know that I want the % figure there?



  Taff™ 10:21 16 Feb 2007

click here Hiding Formulas is possible!

  TonyV 10:24 16 Feb 2007

I had another look at the Formula that was created automatically =(H70-$I$63)/$I$63. Bearing in mind the next input of numbers is below the previous cells, the automatic insertion of the % figures works fine. This now appears in the new % cell after I have inserted the Value in to the Value cell and clicked return. I still can't see how it works automatically, but at least I can see what is doing it. It does not function if you insert data in a cell that is not directly beneath the previous one. i.e. If I inserted the Value in the cell H72 in the formula above, with no data in H71, the automatic bit would not work!

Thanks for your considerations.


  TonyV 10:27 16 Feb 2007

Thanks. It is not a question of a hidden formula, there literally is no formula in the % cell until such time as the Value is fully entered. But the formula above is working in the "Auto" mode fine!



  Simsy 15:20 16 Feb 2007

2 possibilities...

1)Either the formula is in the cell, and you just can't see it until the previous entry is completed, (and this is achievable in a number of ways, eg conditional formatting, hiding and locking, or the ;;; format),


2)The formula is entered into the cell by means of a macro that is activated by entry into another cell.

Is is NOT possible for a cell to suddenly have a FORMULA appear in it by the entry of anything in another cell unless it's with a macro.

You seem to have this ticked as resolved, but you might want to investigate further to see whether this is macro driven or not.



  VoG II 15:28 16 Feb 2007

Is this done by inserting a new row into the worksheet?

  TonyV 15:56 16 Feb 2007

There certainly is no formula in the % cell. It will not work if you miss a row and try the same thing. It must be tied to a similarity function. Incidentally, it can't be a macro since I have only ever recorded one many moons ago and would have grave doubts about doing it now! I've also checked the Macros button and there is nothing shown in there either.

No. I am just coming down the columns row by row. I have just tried another test and found that if I move down say four rows it will not happen and I then have to copy the formula down to give the answer. The only thing I can see as far as formatting is concerned is the in the Protection Tab, the Locked box is checked.

But what ever it is that causes it, it is quite nice in a simple sheet like this one!!


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Adobe shows still-in-development tools, including automatically colourising black-and-white photos

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?