Another stupid Excel problem

  Housten 17:22 22 Nov 12

Good evening,

I hope that some one can see/work out what stupidity I have created. What I have is a ‘master record’ that is created during the day, but it is in a sequential order through the day ( which is how it is made up), but later on I need to sort it into the different accounts. I have been keeping the ‘ master record’ sacrosanct, but copying it to a daily sheet and sorting that. However I have just realised that I can record on to the daily sheet from the ‘master record’ by using the ‘IF’ function to do the sorting for me. So what I have been trying to do is, if the accounts that are on the daily sheet all have to be the same number of rows as the ‘master record’, then by only transferring to a particular account the records for it that are on a particular row. This would mean that a large proportion of the individual accounts would be empty, but the records would be transferred an awful lot quicker, and easier. I have got it to work, almost. My problem is that having the data transferred on to the daily sheet, I then have to do some calculations on this data, and that is where I am having the stupidity.

When I transfer data across to the daily sheet I have been using, say cell C12, “=IF(E100>0,E100,” “)”. What this prevented was an error message appearing in the cell if E100 was 0, which was not in-and-of-itself too annoying, but it was possible to lose sight of some cells calculation. This is all very well but I need to do these calculations on the data – so initially in F12 I had “=E12/C12”, which worked when C12 was greater than 0, but when C12 was 0 the error message appeared! So I have editing this for some time and have now run out of ideas as to what this should be. So far my editing has got me to “=IF(C12>0,(E12/C12),” “). But I am still getting the error messages. What is really annoying, and irritating the h*ll out of me, is that the data being transferred from the 'master record' is both numerical and alphabetical, and when necessary a blank cell is produced, but when purely working on numbers within the daily sheet I am still getting error messages. It just spoils the look of the spreadsheet and – much more importantly - stops me adding up the columns, very necessary.

Can anyone see what the stupidity is that I have committed? I would be grateful for any and all suggestions.

  Woolwell 18:05 22 Nov 12

A possible problem can occur with your first formula =IF(E100>0,E100,” “). This will return " " (a text space) if C12 is not greater than 0. In your later formula you will then be trying to use text to be greater than 0. Try inserting a zero instead of " ".

  mgmcc 19:43 22 Nov 12

Try =IF(C12>0,E12/C12,"")

a) there is no need for E12/C12 to be within brackets

b) the "" marks would not normally have a space between them if you don't want anything to be inserted in the cell. I don't get an error message with that formula.

"initially in F12 I had “=E12/C12”, which worked when C12 was greater than 0, but when C12 was 0 the error message appeared!"

An error would appear because dividing by zero gives infinity, which cannot be calculated.

  Forum Editor 22:24 22 Nov 12

You started a second thread with a it.

I've deleted the thread because gradually the two would become widely separated, and people would have no idea what the second thread was about. Please keep all your posts in the same (this) thread.

  Housten 11:50 23 Nov 12

Forum Editor,

Thanks for correction. I only realised after posting that I had not included everything I should have, and understand it is not possible to edit a post. I would be grateful to know, if I am wrong!!

Woolwell and mgmcc,

Many thanks for your suggestions. I will try these out to see which works!!!

Although I have now come up with another idea, and am having a problem with it I shall create another thread.

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

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

Gear VR vs. Daydream: Which delivers the best VR experience?

Best Mac antivirus 2017