Rewriting a formula...

I have a spreadsheet that calculates totals and has formulas in certain
columns.  Is is possible to have the formulas work, but not have any
numbers in the cells before I input them?  For example, in column D, I
have the simple formula =D9+E9, and so on and so forth down to
=D25+E25.  I may only put itmes in until D11.  So in D12 thru D25, I
have $0.00.  Can I rewrite this formula, possible an IF/THEN statement
to add the two columns, but not show the $0.00 in D12 thru D25, since I
did not have to do any addition?  My main goal is to not have this info
in D12 thru D25 print to make the sheet look cleaner.  Thanks in
advance.


-- 
Roz
------------------------------------------------------------------------
Roz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29746
View this thread: http://www.excelforum.com/showthread.php?threadid=498703

0
1/6/2006 3:15:56 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
465 Views

Similar Articles

[PageSpeed] 35

Perhaps one simple way to achieve a cleaner look in the sheet (as it stands)
is to try switching off zeros display via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Roz" <Roz.217yba_1136560802.3231@excelforum-nospam.com> wrote in message
news:Roz.217yba_1136560802.3231@excelforum-nospam.com...
>
> I have a spreadsheet that calculates totals and has formulas in certain
> columns.  Is is possible to have the formulas work, but not have any
> numbers in the cells before I input them?  For example, in column D, I
> have the simple formula =D9+E9, and so on and so forth down to
> =D25+E25.  I may only put itmes in until D11.  So in D12 thru D25, I
> have $0.00.  Can I rewrite this formula, possible an IF/THEN statement
> to add the two columns, but not show the $0.00 in D12 thru D25, since I
> did not have to do any addition?  My main goal is to not have this info
> in D12 thru D25 print to make the sheet look cleaner.  Thanks in
> advance.
>
>
> --
> Roz
> ------------------------------------------------------------------------
> Roz's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=29746
> View this thread: http://www.excelforum.com/showthread.php?threadid=498703
>


0
demechanik (4694)
1/6/2006 3:51:23 PM
Wow!  So simple...can you believe that I am just an excel beginner?!  :)
Thanks a bunch!


-- 
Roz
------------------------------------------------------------------------
Roz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29746
View this thread: http://www.excelforum.com/showthread.php?threadid=498703

0
1/6/2006 3:59:13 PM
You're welcome, Roz !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Roz" <Roz.218061_1136563253.0938@excelforum-nospam.com> wrote in message
news:Roz.218061_1136563253.0938@excelforum-nospam.com...
>
> Wow!  So simple...can you believe that I am just an excel beginner?!  :)
> Thanks a bunch!


0
demechanik (4694)
1/6/2006 4:08:27 PM
Reply:

Similar Artilces:

Conditional Formatting Nested Formulas
I'm working with a large spreadsheet that has multiple status options. Each status has different criteria for example: Status Green Yellow Red T <60 >=60,<=80 >80 S <20 >=20,<=45 >45 D <30 >=30,<=55 >55 C <90 >=90,<=120 >120 B <30 >=30,<=60 >60 A <10 >=10,<=40 >40 I would like to apply conditional formats according to the status criteria. Is that possible given that each status has a different criterial. Thank you! One way to get going here .. Illustrated in this sample: http://www.freefileho...

Formula to capture worksheet names #2
this is Exactly what I need. I appreciate ya bro -- LowKe ----------------------------------------------------------------------- LowKey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1527 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 No problem. Glad it helped. :) Take care -- firefyt ----------------------------------------------------------------------- firefytr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=664 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 ...

formula help #11
Hi! there, I have a time sheet format for employees and I just don't know how to create the formula for overtime. I only want to input finished time in column C. I don't have much experience in formula function. The timesheet example is below and please help me to solve. Thanks. A B C D E 1 30-01-04 08:00 18:00 1:00 2 31-01-04 08:00 12:00 3 01-02-04 08:00 14:00 5:00 4 07-02-04 08:00 17:00 8:00 5 08-02-04 08:00 12:00 4:00 Column (A) is Date (B) is started time (C) is finished time (D) is over...

How do I get the formula icon back on my toolbar?
I seem to have lost the formula icon off my toolbar and don't know how to get it back so that I can do quick sums in my spreadsheet. How do I do this please? hi are you talking about the autosum icon. if so then.... right click any toolbar and from the popup. click customize. in the customize dialog, click the commands tab. on the left, click insert. on the right, scroll down to autosum right click and hold the autosum icon and drag it to where ever you want it. release the mouse. close the customize dialog. done. regards FSt1 "Andrea confused" wrote: &...

Formula reads date as number
I have a formula that reads a list of dates in cells K5 through K20 an compares these dates to 1 date in cell M3. Cell M3 shows a date bu when I run the evaluate formula auditing tool on the formula, M3 date shows as a number, thus the formula fails. Any hel -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49423 Dates are stored as number of days since 1-1-1900. So today (dec 16,2005) is stored as...

Valid formula remains in cell
I used the function arguments dialog to create and verify the function results in a cell. When I select the OK button, the cell content displays the formula not the results. I cannot seem to locate why. Can anyone tell me why? The cell is probably formatted as Text, format it as General then click in the formula in the formula bar and hit F2, Regards, Alan. "Wally" <Wally@discussions.microsoft.com> wrote in message news:074AA3FE-24A2-4440-86B5-8F3E2689B820@microsoft.com... >I used the function arguments dialog to create and verify the function > results in a cell....

Array formula #2
Hi, I have a list of numbers and I want the average not counting the 0.00 in the range. a 12 15 19 0 101 150 0 52 67 122 Now my formula is {=average(if($a$1:$a$10<>0,$a$1:$a$10,0))} This will sum to 538 and average to 53.8 The result I am looking for is sum 538/8 or 67.25 Where is my error?? TIA ray Ray, Leave off that last ",0": array enter (using Ctrl-Shift-Enter) =AVERAGE(IF(A1:A10<>0,A1:A10)) HTH, Bernie MS Excel MVP "Ray" <anonymous@discussions.microsoft.com> wrote in message news:1757401c448bf$32508e30$a101280a@phx.gbl... > Hi, > I have ...

how to paste a series formula across worksheets?
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel <i>have a budget workbook w/12 worksheets, 1 per month. i have a row of starting totals for each month. how do i copy the starting total in january's worksheet and paste it across the following months' worksheets in increasing months? Ex: in january's worksheet, cell A2 has the total of $100. in february's worksheet, the formula in A2 is: &quot;=100+Jan!A2&quot;. how do i copy/paste the formula across the rest of the worksheets so that they read &quot;=100+Feb!A2&quot;,...

Reference Formula Help
I have a series of data that I'm trying to create a reference for. For example: A B C D 1 2 3 2 4 1 5 3 2 1 4 4 3 2 5 1 Data in Column A is the name or search criteria. Columns B through ... is the range of data I want to search. For this example, for the name or search criteria of "3", I would like to return the number of names or search criteria "3" appears in throughout the data range. So, for this example, 3 appears in names 1 and 4. I tried the initial VLOOKUP search, but realized very q...

VB Formula / Vlookup
Hi I'm trying to create a macro that populates a cell in a worksheet with a vlookup formula, however, the table array is another workbook and is variable. here is my code so far... Let X = Range("factsaccount") & "." & Range("workstation") Range("NAV") = "=VLOOKUP(""Net Assets"",X$C:$D,2,FALSE)" Thanks -Dennis Maybe this technique will help: dim myRng as range set myrng _ = workbooks("otherworkbook").worksheets("otherworksheet").range("c:d") range("nav").form...

Excel Formula #25
I am looking for a formula that will allow me to apply one multiplier up to a certain sum and then a different multiplier after it hits that limit. For example, I have a column of numbers 1, 3, 5, 7, 10. For the sum of the numbers until 10, I need to apply a multiplier of 2. All sums after 10, I need to apply a multiplier of 10. In this case, I would like the responding column to read 2 (2*1), 6(2*3), 10(2*5), 62(2*1+6*10), 100 (10*10) Thanks. -- ATK ------------------------------------------------------------------------ ATK's Profile: http://www.excelforum.com/member.php?action=...

Why does forumula show in cell instead of formula result?
Sometimes when I enter a formula into an Excel cell, the formula itself is displayed in the cell instead of the formula result. However, when I open Formula Wizard, the Wizard shows the correct formula result that should be displayed in the cell. Why does this happen and how do I get it to stop happening? It sounds like the cell is formatted as TEXT. Change the cell format to General. Then, with the cell still selected hit function key F2 then hit Enter. -- Biff Microsoft Excel MVP "Ashley" <Ashley@discussions.microsoft.com> wrote in message news:6...

where can i find a list of excell formula's? #2
I'm learning to use excel more and more and i would like to know how to create a formula that subtracts one cell from another. Is there a list of formula's for excel? Look in HELP contents for function index -- Don Guillett SalesAid Software donaldb@281.com "Art" <Art@discussions.microsoft.com> wrote in message news:DFF58A3A-86CE-415C-A471-0F995F95C108@microsoft.com... > I'm learning to use excel more and more and i would like to know how to > create a formula that subtracts one cell from another. Is there a list of > formula's for excel? Art, y...

Trying to achieve blank cells in formulas
Two questions - similiar nature!! 1. This formula relies on another cell having the appropriate information in which it can perform. However, if nothing is entered in the cell and I want to copy the formula down the column for future entried - What can I include in this formula to ensure that if other cells are empty (nothing to calculate against) that it leaves the cell empty too. Presently, I get N/A# =VLOOKUP(G2,'Data sheet2 '!$A$5:$B$12,2,TRUE) 2. what would the formula be for a simplier calculation i.e =n3*m3 (again if nothing is in n3 what can I add to the formula so it leav...

Problem with draging a formula,one cell value fixed,trivial question
Hi. I have a value in cell A10. Then i have a formula in cell C1 which goes: (A1+B1)/A10. If I drag the formula down to cell C5 the formula for C2 will be (A2+B2)/A11, for C3 will be (A3+B3)/A12,....,for C5 will be (A5+B)/A14. I would like the formula to have the A10 parameter fixed so when i drag the formula down the A10 would always be A10(it wouldn't go up by 1). So the formula for C2 will be (A2+B2)/A10, for C3 will be (A3+B3)/A10,....,for C5 will be (A5+B5)/A10. Please help. Thanks, Marko make your original in C1 formula =(A1+B1)/A$10 then drag down. See help on absolute versus re...

Referencing a macro in an Excel formula
Is it possible to reference a macro in an Excel formula. I want to create a formula that if a condition does not exist in a cell on that line, then delete the entire line. Is a macro the way to go or is there some other way? ...

Copy data into a table (but not the formula)
Hi First of all I apologise if this has been asked before. What I want to know is it possible, and if so how does one do it, to d the following ? Copy data from a cell into another cell in a table but without having formula in the destination cell , e.g Say $b1 contains the name Fred and I want to place Fred into the $c cell but I don't want there to be a formula in $c5 asking for it tha will update the contents of $c5, if I change the value held in $b5, fo instance, I put Fred in $b1 and Fred appears in $c5, but then I pu another name, say Bert into $b1 and it goes to $c6, but the co...

EXCEL FORMULAS #6
I WANT TO SHOW THE ANSWER TO A SUM TO SHOW IN A NONE RELATED CELL.IE:FORMULA SET IN C1=SUM(A1+B2) ANSWER TO SHOW IN D1.CAN THIS BE DONE,IF SO CAN ANYONE SHOW ME HOW. Wyn, Not without using a macro. You can't change a cell with a formula from another cell. You could use =C1 in D1 John "WYN BUNSTON" <anonymous@discussions.microsoft.com> wrote in message news:CEE3C931-8229-44BA-8382-B54D56CF1F64@microsoft.com... > I WANT TO SHOW THE ANSWER TO A SUM TO SHOW IN A NONE RELATED CELL.IE:FORMULA SET IN C1=SUM(A1+B2) ANSWER TO SHOW IN D1.CAN THIS BE DONE,IF SO CAN ANYONE SHOW ...

Tricky Formulas
Hi, I have to perform a tricky calculation using excel. In one cell I have entered the following formula: =(E2*I$2)+(E25*I$3)+(E24*I$4)+(E23*I$5)+(E22*I$6)+(E21*I$7)+(E20*I$8)+ (E19*I$9)+(E18*I$10)+(E17*I$11)+(E16*I$12)+(E15*I$13)+(E14*I$14)+(E13*I $15)+(E12*I$16)+(E11*I$17)+(E10*I$18)+(E9*I$19)+(E8*I$20)+(E7*I$21)+ (E6*I$22)+(E5*I$23)+(E4*I$24)+(E3*I$25) Now, I can;t copy this formula down to the next cell but I am expecting this formula in the next cell down: =(E3*I$2)+(E2*I$3)+(E25*I$4)+(E24*I$5)+(E23*I$6)+(E22*I$7)+(E21*I$8)+ (E20*I$9)+(E19*I$10)+(E18*I$11)+(E17*I$12)+(E16*I$13)+(E15*I...

How to copy a formula without file reference
I have a spreadsheet with formulas lnking cells in different sheets o the same file. When I copy this formula into another spreadsheet , i retains the file reference thus making it useless. Eg. When this formula - (='Sheet 1'!S1420) - is copied to another fil the formula changes as follows: ='[2006 Plan.xls]Sheet 1'!S1420, wher "2006 Plan.XLS" is the file name of the first file. I need to copy this formula to over 60 files running Excel 2000 o Excel 2003. Please help me with this issue. Thanks, Jij -- jij ------------------------------------------------------...

prob with conditional formatting formula
I am basically familiar with Excel 2000 but still a novice. This is th formula that I am trying to conditional format. =IF(SUM(B7:K7)=0,N6+M7-T7,(SUM(B7:K7)+M7+N6-T7)). If after seeing thi so far, your surprised that it works so am I. B7 through K7 ar various income columns. N6 is the previous CASH balance, M7 is anothe cash income column which is not taxed and T7 is a CASH payout column. Question - How do I write the formula in Cond. Format. to show a blan or invisible entry in column N6 if there is no activity in B7:K7 or M7 T7. I can only get part way there with =IF(SUM(B7:K7)=0,N6). ...

change the code to be a formula
i have the following code added to the s/s tab which does what i want but is there a way of adding this to cell like a formula thing like vlookup?? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Column = 2 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Station").Range("A1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0) Application.Ena...

Any way to filter cells with formulas ?
Hi, I need to filter cells with formulas and not to select them , is that possible by a simple or advanced filter ? Thank you . -- gaftalik ------------------------------------------------------------------------ gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6450 View this thread: http://www.excelforum.com/showthread.php?threadid=488232 Maybe.... But I think you'll have to expand on what you really want... gaftalik wrote: > > Hi, > I need to filter cells with formulas and not to select them , is that > possible by a simple or a...

Another Question lol. Formula this time.
Hi again, I have a worksheet that I want to highlight if there is a number greater than zero by changing the text colour. =if(a1>0)colour text red Any advice would be gratefully received Check out Conditional Formatting under Format. Cell value is > Greater than > 0 > OK HTH Regards, Howard "Nevyn" <Nevyn@discussions.microsoft.com> wrote in message news:3935B8CA-745A-4F5D-8504-B450B9E813C2@microsoft.com... > Hi again, > I have a worksheet that I want to highlight if there is a number greater > than zero by changing the text colou...

Re: ROW Formula
Does anyone know how i can change this formula so that i can just dra it down. I have =IF(ISERROR(INDEX(Sheet2!$A$1:$X$569,SMALL(IF(Sheet2!$A$1:$A$569=$B$1,ROW(Sheet2!$A$1:$X$569)),ROW(1:1)),ROW(15:15),1)), ",INDEX(Sheet2!$A$1:$X$569,SMALL(IF(Sheet2!$A$1:$A$569=$B$1,ROW(Sheet2!$A$1:$X$569)),ROW(1:1)),ROW(15:15),1)) And when i drag down i want =IF(ISERROR(INDEX(Sheet2!$A$1:$X$569,SMALL(IF(Sheet2!$A$1:$A$569=$B$1,ROW(Sheet2!$A$1:$X$569)),ROW(2:2)),ROW(15:15),1)), ",INDEX(Sheet2!$A$1:$X$569,SMALL(IF(Sheet2!$A$1:$A$569=$B$1,ROW(Sheet2!$A$1:$X$569)),ROW(2:2)),ROW(15:15),1)) I want the ...