We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Another stupid Excel problem


Housten

Likes # 0

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.

Like this post
Woolwell

Likes # 0

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 " ".

Like this post
mgmcc

Likes # 0

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.

Like this post
Forum Editor

Likes # 0

You started a second thread with a P.S.in 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.

Like this post
Housten

Likes # 0

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.

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Black Friday 2014 tech deals UK Live: Best Black Friday deals from Apple, Amazon, Argos, eBay,...

IDG UK Sites

Black Friday feeding frenzy infects the UK

IDG UK Sites

VAT MOSS: Will I be affected by the EU VAT changes? Here are the facts for designers and artists

IDG UK Sites

Black Friday 2014 UK: Apple deals, Amazon deals & Black Friday tech offers