Yet another Excel question..
Thread Starter
Join Date: Mar 2003
Location: here and there
Posts: 105
Likes: 0
Received 0 Likes
on
0 Posts
Yet another Excel question..
Once again im a little stuck with Excel. This hopefully will be an easy one and I'm just missing something simple.
I've got a basic spreadsheet for money in and out a a groups bank account. People pay in either by direct bank transfer or PayPal. Now PP charge a fee and to keep the figures right, Ive got to allow two columns for these I then have a final 'Total In' column which simply takes away these fees from the original amount. Now the formula im using when copied down the column leaves any lines not filled in as £0.00 is there any way of making Excel just leave those cells empty until something is entered in the cells of the corresponding row.
I've make a quick PS of the Spreadsheet that I'm talking about:
Hope that makes sence, and thanks in advance for any help.
I've got a basic spreadsheet for money in and out a a groups bank account. People pay in either by direct bank transfer or PayPal. Now PP charge a fee and to keep the figures right, Ive got to allow two columns for these I then have a final 'Total In' column which simply takes away these fees from the original amount. Now the formula im using when copied down the column leaves any lines not filled in as £0.00 is there any way of making Excel just leave those cells empty until something is entered in the cells of the corresponding row.
I've make a quick PS of the Spreadsheet that I'm talking about:
Hope that makes sence, and thanks in advance for any help.
Hovering AND talking
Join Date: Feb 2003
Location: Propping up bars in the Lands of D H Lawrence and Bishop Bonner
Age: 59
Posts: 5,705
Likes: 0
Received 0 Likes
on
0 Posts
If it's just a matter of uncluttering the look of the spreadsheet by removing the zeroes, then go to Tools. Option, View tab and untick the zero values box.
Hope that's what you're after.
Cheers
Whirls
Hope that's what you're after.
Cheers
Whirls
Join Date: Feb 2003
Location: Scotland
Posts: 144
Likes: 0
Received 0 Likes
on
0 Posts
You can do it with an if statement.
Say A1 is the cell you want a blank in if B1 is 0.00
put the formula =IF(B1=0.00," ",B1)
If B1 is 0 then A1 will be a space, if it isn't then A1 will equal B1
Say A1 is the cell you want a blank in if B1 is 0.00
put the formula =IF(B1=0.00," ",B1)
If B1 is 0 then A1 will be a space, if it isn't then A1 will equal B1
I would've gone for the IF statement as well, just slightly different:
IF(B1<>"";B1;"")
No need to put a space in there, Excel can identify an empty field this way and also leave the cell empty if B1 is empty
IF(B1<>"";B1;"")
No need to put a space in there, Excel can identify an empty field this way and also leave the cell empty if B1 is empty
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
If you just want fields with zero value not to display £0.00, all you need to do is to set up the "field format" accordingly.
Click on the range of cells where you want to do this,
Click Format - Cells - Number - Custom
Click in the box where it says "Type ... General"
Enter £ #,##0.00 ;£(#,##0.00);
That's pound space # comma # # 0 point 0 0 space semicolon pound bracket # comma # # 0 point 0 0 bracket semicolon space
£123.45 will display as £ 123.45 space
-£123.45 as £(123.45)
£0.00 as
Click on the range of cells where you want to do this,
Click Format - Cells - Number - Custom
Click in the box where it says "Type ... General"
Enter £ #,##0.00 ;£(#,##0.00);
That's pound space # comma # # 0 point 0 0 space semicolon pound bracket # comma # # 0 point 0 0 bracket semicolon space
£123.45 will display as £ 123.45 space
-£123.45 as £(123.45)
£0.00 as
