Problem with Excal 2000 Formula!

  TonyV 14:41 13 Jun 2007

I have a series of worksheets which give me the costs of Fuel, be it Petrol or Diesel, the mpg and then the average mpg over the periods that the tanks are filled. In other words, over a period of time I can see how well or bad the mpg is averaging out.

In Column L in the cells, I have a formula of =IF(H8>0,1,0). This should then give me a figure 1 as the answer if there is a number in the calculated mpg cell, but nothing in the cell if there is nothing in mpg cell. (This is used to get the overall average of the Column L). This works fine in three of the worksheets, but the new sheet I have just created, when the =IF(H8>0,1,0) is copied down the Column L I am getting 1 as an answer in every cell even though to-date there is no data in the worksheet! The sheet is formatted to not show zero's.

What is happening? The L Column is protected under normal use as are all the other columns that have formulae in them.



  TonyV 15:40 13 Jun 2007

Any suggestions?


  TonyV 19:13 13 Jun 2007


  VoG II 19:19 13 Jun 2007

I really don't have a clue!

What happens if you make a copy of one of your 'old' sheets and delete all its data?

  TonyV 19:54 13 Jun 2007

I created the new sheet by copying one that works as I want it, i.e. the 1 comes in when a row of data has been added, then pasting to an inserted new sheet. It now shows the 1 in every cell where the suspect formula is included.

If you wish, I could e mail you a copy of the workbook, and you will see what I am saying. There is nothing that is remotely confidential, it only has mileage readings and petrol prices. Then at the bottom of the mpg column I have the overall average calculated using the column figures.

Thanks for your interest.


  TonyV 20:31 13 Jun 2007

On its' way!


  TonyV 15:36 14 Jun 2007

Thought you might like to know, I saved the file you returned and noticed that beyond Row 60 something, there was another Table similar to the top section, but there was no formulae in the L column. I copied the formula down in the normal way and the 1 still came in with no data in the required cells. To get over the problem, I removed the "extra" bottom table, then inserted rows in the remaining table to bring the length of the table to Row 103. This had the required effect of completing the table so that it maintained its' formatting that did not put the 1 in until there was data in the required cells! I still have no idea why such a phenomenon should happen, but it works fine.



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

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

The secrets of creating gory VFX

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache