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.

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

The updated 'Corel Painter inside Photoshop' plugin ParticleShop offers new brushes

New MacBook Pro 2017 release date, UK price and tech specs: Kaby Lake MacBook Pro to launch in…