Problem with Excal 2000 Formula!

  TonyV 14:41 13 Jun 07
Locked

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.

Thanks

TonyV

  TonyV 15:40 13 Jun 07

Any suggestions?

TonyV

  TonyV 19:13 13 Jun 07

bump!
TonyV

  VoG II 19:19 13 Jun 07

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 07

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

  TonyV 20:31 13 Jun 07

On its' way!

TonyV

  TonyV 15:36 14 Jun 07

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.

Thanks

TonyV

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

Surface Pro 5 News - release date, UK price, features, specs

Microsoft Surface Studio hands-on review: the iMac killer is here

Best Mac antivirus 2017