how do I default sum to 0 for negative numbers in excel


        
0
ej764 (1)
11/16/2004 5:26:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
315 Views

Similar Articles

[PageSpeed] 41

One way"
If(SUM(A1:A10)<0,0,SUM(A1:A10))

tj

"ej764" wrote:

> 
0
tjtjjtjt (488)
11/16/2004 5:29:02 PM
Try

=MAX(0,SUM(F2:F300))
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"ej764" <ej764@discussions.microsoft.com> wrote in message
news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com...
>


0
Don
11/16/2004 5:33:15 PM
Hi

=SUMPRODUCT(--(A1:A10>0),A1:A10)


Arvi Laanemets


"ej764" <ej764@discussions.microsoft.com> wrote in message
news:5FBED33E-FC8F-4E0B-9E1B-AB63205D589E@microsoft.com...
>


0
11/16/2004 5:39:11 PM
Reply:

Similar Artilces:

Auto chaging to defaults
XP Pro SP3 and IE8 both with all updates. Every few days the following happens. 1. Home changes from yahoo.com to msn.com and bing.com 2. Several entries appear in my favorites. They are all MSN entries. I change the home back and delete the MSN entries from favorites. All is fine for several days and then it happens again. Any ideas ?? bwilkie@horizoncable.com Uninstall MSN/Windows Live/Bing Toolbar & reboot. bill wrote: > XP Pro SP3 and IE8 both with all updates. > > Every few days the following happens. > > 1. Home changes from yahoo.com...

run-time error 8007007e 8.0 support administrator's console
Every time I try to run it I get this error. Works fine on other computers in the area. Using XP Pro, SP2. HELP ...

Password excel 2007
How does one set up up a password for the excel file I have currentley have as a short cut' Thank you bill - Open the file (Office Button | Open ... ). To password-protect the workbook, choose Review (ribbon) | (Changes section) Protect Workbook. Enter a password. Click OK. Save the file. To encrypt the file using a password, choose Office Button | Prepare | Encrypt Document ... - Mike http://www.MikeMiddleton.com "bill" <bill@discussions.microsoft.com> wrote in message news:B14BA33B-26BF-4375-9A39-DD7E6435AC04@microsoft.com... How does one ...

deleting duplicate files from Excell
Can anyone tell me how to delete multiple files in an Excell worksheet. It is a very large database with many multiple files and was wondering if there was a macro i could run Thank you Gloria at Habitat for Humanity You can select the whole lot, then do data>filter advanced filter, copy to another location (click a cell somewhere), and finally check unique records only -- Regards, Peo Sjoblom "Habitat for Humanity" <anonymous@discussions.microsoft.com> wrote in message news:0a9301c3a7b0$2acd82e0$a501280a@phx.gbl... > Can anyone tell me how to delete multiple...

Business Portal 2.0 Time & Attendance Problem
Hi everyone. I've recently implemented Time & Attendance in Great Plains 7.5 on SQL and now I'm setting up the Business Portal so that our employees can submit their time electronically. I'm aware that the business portal displays the vacation and sick time using the two fields in the UPR00100 table and I have a trigger setup so that it will continually update those fields from the TATM1030 master table. This keeps me from having to run reconcile every time someone makes an update. What I'm experiencing now is that any timecard submitted and approved is making it...

Tracking Number on SOP Invoice Form
I am trying to get the Tracking number that is entered in the Sales User Defined Fields Entry Window onto the SOP Long Invoice Form. Does anyone know what tables to create the relationship with? I know the SOP10107 table holds the tracking number into and I was able to get the table in the table list but i must have the incorrect relationship because nothing appears on the report when i add the tracking number. -- Thanks Knavas Hello Knavas, If you are on GP 7.5 SP2 or higher you can refer to KB article 874762. If you are on GP 7.5 SP1 or lower you can refer to KB 859212. Both art...

How can I expose the actual column & row headers in excel?
We have a timesheet program an employee created for our company. She hid all the formulas and calculations. I have unprotected the sheet and am trying to adjust the code but the acutal headers are hidden and I cannot highlight them to unhide them. (ex: the actual column headers, ABCD, ets & the row headers, 123) Juanita Wrote: > We have a timesheet program an employee created for our company. She > hid all > the formulas and calculations. I have unprotected the sheet and am > trying to > adjust the code but the acutal headers are hidden and I cannot > highlight...

validate a field based on sum of other fields
I have to assign commission to agents for each "order". I can have anywhere from 1 agent at 100% commission to 5 agents with every possible combination of percentages. Obviously, I need the percentage entries to only add up to 100 and I'd like to force the user to adjust the figures until they total 100. Firstly, am I right in assuming it would be better to be assigning the agents to the orderIDs in separate records: OrderID, Agent, Agent Percentage (as opposed to having one record contain the OrderID, Agent1, Agent1Percentage, Agent2, Agent2Percentage, Agent3, Agent3Pe...

Excell defaults e-mai to expressl
In excel spreadsheet, the embedded e-mails are defaulting to Outlook Express which is not my e-mail server. Thank youi. What version of Outlook do you have installed? Is it set as your default email client? Do other email links (such as on web pages) bring up outlook? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRI...

Is Excel unreliable ?
Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ I don't understand what you mean by 'drag down the calculated cell' Also could we see the particular formula that Excel is getting wrong. I'm not aware that ...

Change to foreign currency in POS 1.0 SP 1a
I am new to MS Dynamics POS and would appreciate assistance in changing the default currency from US$ to Nigeria currency. Simply, I would like the $ symbol change to N. Thanking you for your assistance. Thank you folks. I found the answer in Junaid Idrees'response to Tim Simth's post on 7/24/06. "Sokan33" wrote: > I am new to MS Dynamics POS and would appreciate assistance in changing the > default currency from US$ to Nigeria currency. Simply, I would like the $ > symbol change to N. > > Thanking you for your assistance. ...

Difference between basing pivot on excel list or other pivot table
What is the difference between basing the pivot table on excel list of other pivot table report? thanks Hi Gio The big pro for basing off another Pivot Table is the reservation of memory. The big con is that both are linked. This means much of what you do to one, effects the other. Perphaps the one that most get caught with is the 'grouping' of data. Group one Pivot Table and any others based off it will also be grouped. I will often base one PT off another to save the user from having to drag fields about. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins ...

MS Excel 2000 Cell Capacity and Data Display
I'm working with a large Excel 2000 spreadsheet in which most of the cell entries are text. Some text cell entries exceed 300 words. I'm using the wrap text and row/column autosizing features. All of the text displays correctly when I'm in the cell or in the formula box. When I move to anyother cell, however, the text in the cells is cropped. How do I get all of the text to display and print properly? Thanks. If you add alt-enters every 80-100 characters, you can see lots of characters. You may have to manually adjust the rowheight, though. RCM-IEWTD wrote: > &g...

Page numbers in Word
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I cannot get Insert Page numbers to work in word, in documents elements when I choose Header, in other words, in any format. When I select Insert, etc. I get a grayed button reading "page" instead of the numbers. I even had an email response from Microsoft but their instructions did not correct the problem. They had me put several items in the trash, and finally restart. Nothing worked. It sounds to me like you simply may have toggled the display of Field Codes -- Try keying Option+F9 or go into the Head...

How can I show values, not formulas in Excel?
When I add a VLOOKUP function and fill down, the formula, not the resultant value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in not checked. Thanks! Could be that your cells are formatted as text, format as General. HTH JG "jwbass" wrote: > When I add a VLOOKUP function and fill down, the formula, not the resultant > value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in > not checked. > > Thanks! Make sure it is not formatted as text -- mphell0 -------------------------------------------------------------...

Automatic numbering of invoices
Good morning folks, What is the secret to automaticaly update the invoice number in a cell when I create a new invoice. May look simple to most but not for a green like me. Thank you The secret (I think) is to decide where to store the last invoice number and when to update it. If you search google for invoice template, you'll find lots of suggestions. http://groups.google.com/groups?as_q=invoice%20template&as_ugroup=*excel* wooptidooo wrote: > > Good morning folks, > > What is the secret to automaticaly update the invoice number in a cell when > I create a new...

Integration Manager 10.0 Error: Object Reference not set to an instance of an object
Hi, Any body have any solution for the Integration Manager for MS Dynamics GP 10.0. I'm receiving an Error when i run Integration Manager Error Message: The Destination could not be initialized due to the following problem: Object reference not set to an instance of an object. I find a hot fix on Microsoft site, HotFix contains only one DLL file and Readme file After applying the hotfix by replacing the DLL Microsoft.Dynamics.GP.IntegrantionManager.cbomcommon.dll the Error changed to 'Registration Keys Expired' I did not entered any registration keys Yet. Try the following: 1....

NZ Function and Null VS 0 values
Here is my question, I have the following statment in a query: PostSumCreated: Sum(NZ([Post_Sec2_7CreatedTotal]) +NZ([Post_Sec3_5CreatedTotal])+NZ([Post_Sec4_3CreatedTotal]) +NZ([Post_Sec4_5CreatedTotal])+NZ([Post_Sec5_4CreatedTotal]) +NZ([Post_Sec6_7CreatedTotal])+NZ([Post_Sec7_4CreatedTotal])) This returns either the number of the addition or 0. The problem is that I have NULL values in the table, where 0 would represent that yes nothing was done and Null would represent that nothing was support to happen. What I need help with is trying to add the needed columns if they have a value >...

how do i use the excel symbol toolbar?
Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the easy insertion of commonly used symbols. The problem is, I can't figure out how to use it. I have tried dragging the symbols from the toolbar onto a worksheet, copying them, double clicking them, etc... Nothing works. Does anyone have any idea how to do this? It's not even in Excel Help. Select the symbol and click insert button Regards, Peo Sjoblom "Gwhizkids" wrote: > Excel 2003 has a Symbol Toolbar that looks like it is designed to allow the > easy insertion of commonly used sy...

Find all cells with a number and mulitply
How can I find all cells in a sheet that are a number greater than zero and multiply each one by a number? IE: the number to multiply by is 1.2 A1 Tom A2 25 A3 0 G12 Bob G13 25 I want to programatically (without using a macro) find cells a2 and g13 only and multiply them by 1.2 Thanks in advance for your help. Jim Jim, As long as you don't have any negative numbers, it doesn't matter about being > 0 (0*n=0) or being text. Try this Put 1.2 in a spare cell and then copy it Select all the cells Goto menu Edit>Pastespecial Click the Multiply option OK out Now clear the c...

Auto Page Numbering Question
I have a 12 page pricelist. Recently we made changes to the last 4 pages of this sheet. I am not getting ready to have the last 4 pages printed and sent to my customers so that they can simply replace the last 4 pages, leaving the first 8 in tact. I copied the last 4 pages into a new book, and need the page numbers to read 9 of 12, 10 of 12 ect. However, because the pages are in a new book I can only figure out how to have it say 1 of 4, 2 of 4 and so on. Basically, I need to change the footer to read 9 of 12, 10 of 12, 11 of 12 and 12 of 12, but this is only a 4 page sheet. ...

Excel 2003 math error
I have a cell that evaluates to 318.43. When I multiply that cell by 156, Excel gives me 49674.51, but the correct result, when I do the math by hand (and by Google), is 49675.08. What the ___ is going on? Hi Try extending the number of places of decimal on the first cell, and you will probably see that it is not 318.43 but something smaller (318.4263) which has been rounded to 318.43 With your evaluation formula that results in a displayed value of 318.43 in say C1, try =ROUND(C1,2)*156 -- Regards Roger Govier <cyraxote@gmail.com> wrote in message news:8801150f-5557-412d-9143-9...

default save directory for a templated worksheet
Is there any easy way to automatically have a template save to a particular location for that template only? e.g the 'Quotes' template saves to the quote directory the Orders template saves to the orders directory Thanks Mike Please clarify: do you want to save the TEMPLATEs to those directories, or do you want to save the files CREATED FROM THE TEMPLATES to those directories? If the latter, you could do it with code imbedded within the template. "Mike Lang" wrote: > Is there any easy way to automatically have a template save to a particular > location for th...

WDS 4.0 and Searching while Typing
I have a system that has Windows XP SP3 with Office 2007 SP2 and WDS 4.0. For some reason the function of searching while typing when using the advanced search in Outlook has stopped working. The only way to get the search to start is to click in the magnifing glass. I have not tried to do a repair of Office or reinstalling WDS 4 as I thought there might be a quick fix such as a setting in Outlook or WDS but I cannot find one. "Crockett" <Crockett@discussions.microsoft.com> wrote in message news:F262FD29-C540-4037-8AB0-F9ACDE078781@microsoft.com... > I ha...

bug in Excel?
Hi! I created a worksheet which is linked to two different "source" workbooks to consolidate data. I am using this workbook for several consolidations (same format and i just save the changed data under a file "source 1" and "source 2" for then automatically obtaining the sums in my CONSOLIDATION workbook) and that works fine. But after consolidating different workbooks and obtaining 3 different CONSOLIDATED files (which i save under another name after consolidation) for year1, year2 and year3, the values in those files change as soon as i open another one!...