Jump to content

MS Excell


PladdPezzPunk

Recommended Posts

So what i am trying to do is make two sheets, both 20x18, then on sheet 3 make a sheet with the average of the first two sheets. The problem I am having is making the conditioning formula in sheet 3 so if there is no numbers in one of the cells in sheet 1 or 2, in sheet 3 a 0 is entered. how it would go is sheet 1 a1 and sheet 2 a1 is blank, then in sheet 3 a1 a zero should be entered. If sheet 1 in a1 is 5 and sheet 2 a1 is 10, then in sheet 3 a1, 7.5 should be the average.

 

I know how to make the average formula but the condition to make blank cells = 0 is something i can't figure out. Someone please help.

Link to comment
Share on other sites

Select the cell you want to show a zero. Then rightclick and choose cell format. Choose the right format that will make the empty cell show a zero. It worked for me with "standard cell format". maybe you are in monetary?

 

Can you help with that question :

 

http://www.silveradoss.com/forums/index.php?showtopic=27293#

 

Thanks buddy

Link to comment
Share on other sites

yes i will do that. I should be able to get nice pics on saturday of this repair. Problem is i'm at work during all daylight hours. I'll PM you when i post the pics.

 

on excell, i need to make a conditional statement though. it will show a ref error message if both of the cells in sheet one or two are blank. Basically since there is no data it just thinks it's an error. I just want a 0 if it thinks there is an error otherwise i want the average.

Link to comment
Share on other sites

Humm, you probably have an error in you average formula then. Otherwise it sould read a 0.

 

My average formula goes ike this : "=(Feuil2!B1+Feuil1!A1)/2"

 

Feuil stands for sheet, I've got the french version.

 

example (1+2)/2 = 1.5

 

The "=" is very important

 

Then you copy that downwards with the little cross that appears when you point the bottom right of the cell with the formula.

Link to comment
Share on other sites

formula right now is "=average(sheet1!a1,sheet2!a2)" this works great if there is values in either of the cells a1 or a2. but some of the cells in the sheets are blank. problem being that it enters an error message if there is no value in both cells. I need to make a logistical statement. the statement in plain english would have to be "If any number value then average two values. If no values in either cell then enter 0"

 

It's hard to really explain. maybe I'll post the spread sheet. that will show it more logically.

Link to comment
Share on other sites

excel spreadsheet

 

This is what i have so far. in sheet 3 i need the average of the first two sheets and if there is no numbers in one of the sheets, then i need the sheet with a number to be transfered to sheet 3. also if there is no numbers in both of the corresponding cells in sheet 1 and 2 then in sheet 3 i need a 0 entered. any ideas would be great.

Link to comment
Share on other sites

Ok got it I think.

 

Make a first column with the average. Then make a second column, that's the one you will use. The second one will put a zero if the first shows a division by 0.

 

That's the formula for the second : =SI(Feuil2!B2=0;0;Feuil3!C2)

 

It's probalby =IF(sheet2!B2=0;0;sheet3!C2)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...