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).  Pleas
excuse for being so wordy. Thanking you in advance for you
consideration - Frony

--
Message posted from http://www.ExcelForum.com

0
3/1/2004 9:08:55 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
595 Views

Similar Articles

[PageSpeed] 12

Hi, 

I dont know if this is what you need , please try :

=if(or(sum("your range")=0;sum('your2nd range)=0;"";N6))

I hope this will help you 

Rgds,
Gaftali

--
Message posted from http://www.ExcelForum.com

0
3/1/2004 10:06:45 PM
Sorry i forgot to explain , 

That will return a blank cell if any of the sum of those range
selected by you are equal to 0 , if not they will return cell N6

Good luck !
gaftali

--
Message posted from http://www.ExcelForum.com

0
3/1/2004 10:08:18 PM
gaftalik wrote:
> *Hi, 
> 
> I dont know if this is what you need , please try :
> 
> =if(or(sum("your range")=0;sum('your2nd range)=0;"";N6))
> 
> I hope this will help you 
> 
> Rgds,
> Gaftalik * 

Hi Thanks for the response - I tried it and checked it twice and got
back "The formula you typed contains an error".  Is this the formula
that I am to type in the formula section under or in conditional
formatting.


---
Message posted from http://www.ExcelForum.com/

0
3/1/2004 10:22:06 PM
gaftalik wrote:
> *Sorry i forgot to explain , 
> 
> That will return a blank cell if any of the sum of those range
> selected by you are equal to 0 , if not they will return cell N6
> 
> Good luck !
> gaftalik * 

That would be what I want.  Now for the stupid question did you mea
for this formula to be typed into my worksheet or in to the conditiona
formatting formula section

--
Message posted from http://www.ExcelForum.com

0
3/1/2004 10:26:51 PM
gaftalik wrote:
> *Sorry i forgot to explain , 
> 
> That will return a blank cell if any of the sum of those range
> selected by you are equal to 0 , if not they will return cell N6
> 
> Good luck !
> gaftalik * 

That would be what I want.  Now for the stupid question did you mea
for this formula to be typed into my worksheet or in to the conditiona
formatting formula section

--
Message posted from http://www.ExcelForum.com

0
3/1/2004 10:43:28 PM
Reply:

Similar Artilces:

Change default font format in Excel
I have an existing workbook with cell text entries in regular black font. I want to make a number of additional entries in various cells in this workbook, and I want all my text entries to be a different font format (bold, red). Is there a way to do this automatically without highlighting each entry I make and manually changing the cell format? Thanks. Michael, Copy the code below, right-click on your sheet tab, select "View Code" and paste the code in the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Cou...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Combining cells to create a formula
Hi, I have two cells that I want to combine to have a working formula B1 = sum B2 = d3 b3 = =b1&"("&b2&")" D3 = 10 The result in b3 is : sum(d3) How do I get this to result in the actual value in D3. I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here. Thanks Hi I think you are perhaps looking for the Indirect function =INDIRECT("D"&ROW(3:3)) would return the value contained in D3 As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc. -- Regards R...

Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting" checked under "PivotTable Options", and "Autoformat Table" unchecked. Even so, I either lose portions of my formatting, or different formatting is applied when I refresh. Does anyone know what I am doing incorrectly or have any other suggestions? Thanks, Phil Other things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. --Instead of selecting the cells to format the numbers, right-click the field but...

Formula Question #18
I have built a workbook in which I have inserted a formula to tell me whether the contents of a supply bin needs replenishment or not. The formula I used is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that ...

in excel how can we put formula to convert numericalfigureto word
for example : in excel i have mention 25000.00 in numerical amount , i want to know how can i convert in next colum , about word ?/; How can i put formula to make the numerical in to words like 25000 in numerical to twenty five thousands in word. There is no direct functions to convert this. For a VBA solution check out the below links http://support.microsoft.com/kb/213360 http://www.ozgrid.com/VBA/ValueToWords.htm http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "excel" wrote: > for example : > > in excel i hav...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

formula: counting presence
Hello! I have to count presence of employees from sheets between START and END, which is stored in G9 cell. I think it should be something like: =SUM(IF(START:END!G9="present"; 1; 0)), but this one returns #REF and I don't don't why. Try these from a post of mine today. One way. Put the sumif on each sheet with an indirect reference to d12 of the master. then use =sum(sheet1:sheet21!a2) where a2 in your sumif formula. One way to put=SUMIF(B:B,Sheet1!D12) on each sheet is to select all>type the formula in the cell desired>after the error msg>delete from the ...

Formating
Hi, In a Column 'A' sales Commission is calculated and resulta is as 250, 200, 300, 330. I wanted to format a cell with a Blinking colours where value is 100 to 250, 250 to 300, 300 to 350 and 350 & above. please help to format my sheet ... thanks Ismail, Getting your cells to "blink" is not in the standard Excel formats What you want would require some extensive programming, certainly, ou of my capabilities. However, there are some great things you can do with Excel' CONDITIONAL FORMATTING. Let's say in column A you have numbers that are the result of formu...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

How do I set up a formula in excel that is the tenth root of 7 ve.
How do I set up a formula in excel that is the tenth root of 7 versus the square root of 7? try the formula =7^(1/10). HTH Alan P. "kestig1" <kestig1@discussions.microsoft.com> wrote in message news:99C16AB7-8BD6-4671-B138-5F2961E46067@microsoft.com... > How do I set up a formula in excel that is the tenth root of 7 versus the > square root of 7? ...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Opening and formating a CSV file?
Could someone remind me how to handle CSV files please? I had assumed the Text Import Wizard would pop-up when I use File|Open, but the data goes straight into the worksheet. (Presumably because it is not 'delimited'?) Without the Wizard at my side, how do I get each comma-separated field in its own column? -- Terry, West Sussex, UK Try renaming your .csv file to .txt Terry Pinnell wrote: > > Could someone remind me how to handle CSV files please? I had assumed > the Text Import Wizard would pop-up when I use File|Open, but the data > goes straight into the worksheet...

Problem access variable in On Format
Hi, I'm using Access via Office XP Pro. I am trying to format the Zip Code on the detail line of my report. I have tried the following lines of code in both the On Format event and the On Print event: If len([PostalCode]) > 5 Then : : end if or if len(Me.PostalCode) > 5 Then : : end if In both events and either code, I receive the error message: Access can't find the field 'PostalCode' referred to in your expression. If I put "PostalCode" as the source of the report's control...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Copy/Paste formulas
How do I keep certain cells (those I want to point to a specific 'constant') from incrementing while the remaing cells in my formulas increment as expected. Example: ((E65)*(COUNTIF(I7:I7,"V"))) where the cell "E65" contains a set value that I want to be placed in the result as I step down the incremental (I) rows when the character "V" is found in the particular (I) row. When I do my copy and paste, the (E) row increments as the (I) rows increment. $E$65 "BobG" wrote: > How do I keep certain cells (those I want to point to a specific...

Formula for competition timesheet
Here is the situation. I have a number of members in a clay target club who shoot a competition over a number of ranges. Ranges 1 to 8. They shoot a competition over 4 days. They start shooting at a specific time each day. Start time in cell A1. The duration of the time they spend on each range is specified in B1. These times may vary each day. I have set up a table in the worksheet that shows the squad numbers in column A, the ranges they shoot each day and the time they start to shoot on each range. This table only shows the squad numbers up to the number of members shooting, which is ...