How to solve a formula so that it returns actual answer if negati.

I am trying to write a formula so that it displays all negative results but 
defaults to zero if the answer is any positive number.
0
Wayne (81)
12/22/2004 7:33:05 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
428 Views

Similar Articles

[PageSpeed] 33

Hi
try
=MIN(your_formula,0)

-- 
Regards
Frank Kabel
Frankfurt, Germany

Wayne wrote:
> I am trying to write a formula so that it displays all negative
> results but defaults to zero if the answer is any positive number. 


0
frank.kabel (11126)
12/22/2004 7:44:15 PM
Hi. Try:

=MIN(your_formula,0)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am trying to write a formula so that it displays all 
negative results but 
>defaults to zero if the answer is any positive number.
>.
>
0
jasonjmorin (551)
12/22/2004 7:46:33 PM
Reply:

Similar Artilces:

< > formula
I am trying to obtain a cell in which the cell value will be limited t a num greater than 0 but less than 3. for example cell A has a formula which produces a value in cell B, bu the value in cell B cannot be negative nor go above 3. Does anyone have any insight in this are -- David Morale ----------------------------------------------------------------------- David Morales's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1600 View this thread: http://www.excelforum.com/showthread.php?threadid=27471 Hi David 1) A formula in A can not produce values in B. Only...

Formula To Split Name
I have a list of names in colum B, that I would like to split into column C and D. The names in column B have a first name, middle intial and a last name. I would like the last name to go into column c and the first name and middle initial to go into column D. Column B Column C Column D Mary A Jones Jones Mary A Hi, Put this in column C =RIGHT(B1,LEN(B1)-FIND("*",SUBSTITUTE(B1," ","*",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))))) Then this in column D ...

Adding a Formula as a data variable in a pivot
Is it possible to create a formula as data column in a pivot table? For eamaple let's say I have the following Table: Name Billed Charges Paid Charges George 1000 500 Pilar 2000 750 Fred 1211 602 I would like to add a data variable of [Billed Charges] - [Paid Charges] and another [Billed Charges] / [Paid Charges] -1 I know that one way to do this is to add columns to the raw data and refresh the pivot, but I am wondering whether I can create a calculated formula as...

XmlReader.Create always returns {None}
I am trying to create an XmlReader using XmlReader.Create, but it always returns {None}. I have tried several examples from the web, and Create always returns "{None}". Here is my code: XmlReaderSettings settings = new XmlReaderSettings(); settings.ConformanceLevel = ConformanceLevel.Fragment; settings.IgnoreWhitespace = true; settings.IgnoreComments = true; XmlReader reader = XmlReader.Create("C:\\MyFile.xml", settings); Does anyone know what is wrong? Thanks. Joe Rattz wrote: > I am trying to create an XmlReader using XmlReader.Create, but it always > ...

Is it possible to shorten this LOOKUP Formula?
I have 3 groups of Drivers, all of them work a for day work week. Group 1 works Monday-Thursday, 2 works Tuesday-Friday, and 3 works Friday, Saturday, Sunday, Monday. Group 1 with Friday off has assignment numbers of 900-949, 2 with Monday off has 950-999, 3 does 900-949 on Friday and 950-999 on Monday. (The weekend numbers are totally different and not included in this process). On my spread sheet I use these numbers in column A to time track the drivers as they pass various locations. Column H is where I place the names of the drivers. I have H set to change names for the different d...

formulas show up instead of values
I have data that came from a mainframe. The cells aer formated as General. If A1 is 3 and in B1 I enter =A1, what I see in B1 is =A1 instead of 3. I've reformated A1 to a format of number but it doesn't change B1 - it still says =A1. I do not have view formulas turned on under Tools/Options/View. What else could this be? Change the format of B1 to general (it's probably set to text at this time) -- Michael Hopwood (Phobos) "Sherry" <nowhere@microsoft.com> wrote in message news:#iXHnsrsDHA.1196@TK2MSFTNGP12.phx.gbl... > I have data that came from a mainframe...

Excel sum formula #2
Hi everyone. This is my first time here. I actually have a little bi of difficulty for a work that I have to do... It goes like this: O the workbook, I have differents pages. In theses differents pages, have a number on the first second and 3rd page wich is 300300 in th first colum. I'd like to get the sum of the 3 display next to th number 300300. There is an exemple: (First page) (second page) 102120 -5689 156460 -5879 012110 -3956 254680 -4568 300300 -4564 300300 456846 So what i...

GetDocTemplate() returns NULL in CDocument::DoSave()
Hi all, In our application, when we do a SaveAs(), our derived class calls CDocument::OnFileSaveAs(). The problem is that in some instances, the call to GetDocTemplate() in CDocument::DoSave() is returning NULL. Causing an ASSERT. It doesn't do this in all cases, just some. I am trying to track down why the document template hasn't been set correctly, but I guess I don't understand where it is set for the document. Is it just in the AddDocTemplate call in InitInstance? Does anyone have any idea why it wouldn't be set in this particular case? Can someone give me some hints as ...

Returns Transaction Entry Help not linked to window
For some reason, when I click on the Help icon on my Returns Transaction Entry window or the Purchasing Returns Distribution Entry window, nothing comes up. However, I know one exists at least for the Returns Transaction Entry window bacuase I can get to by calling up Microsoft Dynamics GP Purchase Order Enhacements Help from the Returns Batch Entry window. Is anyone else having this problem? Any suggestions on how to link these together? Thanks for your help. ------=_NextPart_0001_ABE797D9 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello, Thanks for posting to Newsgrou...

Return value using partial value of a cell.
Can anyone tell me how to use some logic to return a value from a partial cell reference? Here is what I have tried, as you can guess they didn’t work. Is there a wild card that could be put before and after in the formula? (That is what the % was all about in my first attempt.) note column B is where i am trying to return the value from the formula. =if(a1=%her%,”her”,0) I also tried… =if(a1=”her”,”her”,0) A B 1 t-shirt small her black her 2 t-shirt med him black 3 t-shirt med her black her On 8 mar, 23:03, Shaun <Sh...@discussions.microsoft.com> wrote: > ...

Is there an answer to why Outlook 2003 won't "send" email?`
I've reviewed postings for past few days, including my own from 11/22, and found no substantive answer to making Outlook email send. Also, finding this program may be more trouble than it's worth. Can anyone out there, especially you Microsofties, explain how to use this confounding program. Is there any kind of troubleshooting class that would be less confusing than the program? Help! What are you doing to try and send mail and what errors do you receive? We also need to know your OS, the SP's that are installed and any antivirus or firewall software that is installed. --...

How to use autofilter in excel with formula reference changing
Hi, I have a table A B C 1 forro 120 2 ment 80 40 3 forro 50 30 4 ment 40 10 5 ment 30 10 6 forro 20 10 7 forro 10 10 8 forro 5 5 Starting from C2, the column C has a formula (=B1-B2) result = 40; (=B2-B3) result = 30; etc... When I apply autofilter the formula in cell C keeps the original information (=B1-B2), and I would like to have a formula to change and shows de result as below (=B1-B3) result = 70; (=B3-B6) result = 30; etc... A B C 1 forro 120 3 forro 50 70 6 forro 20 30 7 forro 10 10 8 forro 5 5 The objective is to have a formula considering jus...

Inserting rows with Data, Formula's and Validation
Hi all, I posted this twice earlier today, but it hasn't appeared so if it triple post please forgive! I have a spreadsheet that starting at row 18 contains the following information A18=M$2 B18= Datavalidation indirect ($M$2) C18=IF(B18="No More Options",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),IF(B19<2,VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),CONCATENATE(B19," x ",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE)))) E18=IF(ISERROR(VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info"))...

formula for start
I am designing a spread sheet for staff payroll. Enter start time and finsh time, if total is greater than 8 hours hours paid will deduct half an hour off for unpaid break. New to formulas and i can't get this one sorted apreciate any help Assuming start time is in cell A3, finish time is in cell B3, in cell C put this formula =IF((B3-A3)>TIME(8,0,0),(B3-A3)-TIME(0,30,0),B3-A3) Format all cells as Time > 13:30 and enter all times using the 2 hour format where 18:00 = 6:00 p.m. (this eliminates the need fo entering A.M. or P.M. for each entry) ht -- swatsp0 ---------...

need to hide formula ...
I have a problem with a Formula that gives me a #VALUE! but would like this to be hidden. Earlier I had posted for help on #DIV/0! and I the response was great it worked and the problem was fixed. But I realized that later this cause a problem in my cell next to it. So this is how the whole thing is actually, on colom C cell-A I have this formula: =ROUND(D5*20,0)/20 on colom D cell-A I have this formula: =$A$5/SUM($B$5:$B$103)*B5 Previously I received a solution for colom D cell-A and it went like: =IF(SUM($B$5:$B$103)*B5=0,"",$A$5/SUM($B$5:$B$103)*B5) Worked wonderfully. But...

BUGS ! or how to return soft to Microsoft and get full refund
Tried to tell Microsoft about these BUGS for TWO! years now - still no solution. I am prepared to bet $10 these bugs will be there in Money 2005. If any of you want to return Money 2004/2005 to microsoft and ger refund call them about bugs below Bug #1. When you enter purchase transaction for a bond you input "accrued interest" (lets say $100). This accrued interest is not used in any returns/tax calculations. So if you sell this bond (with increased accrued interest) or have coupon payment (lets say $400) Money will show income of $400 !!! It should show $400-$100=$300 B...

Simple formula for newbie!
Hi Simple question, as just starting with formulas!! But.. I need a formula whereby if cell D1 >= 10, then multiply E1*D1. Else multiply F1*D1. Thanks! TC Try... =IF(D1>=10,E1*D1,F1*D1) Hope this helps! In article <#GK8G$#oFHA.3316@tk2msftngp13.phx.gbl>, "TC" <trevc80@hotmail.com> wrote: > Hi > > Simple question, as just starting with formulas!! But.. > > I need a formula whereby if cell D1 >= 10, then multiply E1*D1. Else > multiply F1*D1. > > Thanks! > TC TC You'd almost got it! Try - =IF(D1>=10,E1*D1...

need help with formula #10
I'd appreciate help with a formula I need a formula to calculate the following 4.5% of any amount up to and equal to $41,100.00 PLUS 6.00% on an amount above $41,100. ie $20,000 would be (20,000 x .045) $900.00 ie $50,000 would be [(41,100 x .05) + (8,900 x .060)] $2,383.50 Thanks Pete -- pgruenin ----------------------------------------------------------------------- pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2601 View this thread: http://www.excelforum.com/showthread.php?threadid=47876 I used =IF(A1>41100,(A1-41100)*6%+(41100*4.5%...

How do I do formulas?
Hi, How do I do formulas? I want to change numbers to forlumas. How do I do this? Can it do done? Thank You. Bryan If you mean you want the sheet to display the formulas instead of th reults you can do it by pressing the Ctrl key & the ' key or Tools Menu > Options > View Tab > Tick Formula -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=27107 Also, make sure your Formula b...

Gross Margin Formulas
Does anybody have a formula for gross margins? I also believe there should be a link to basic accounting formulas for first timers assuming cost is in A1 and price is in B1, =(B1-A1)/B1 format cell as percent -- Regards, Peo Sjoblom "newman" <anonymous@discussions.microsoft.com> wrote in message news:7bd201c402da$08323250$a401280a@phx.gbl... > Does anybody have a formula for gross margins? > > I also believe there should be a link to basic accounting > formulas for first timers > > ...

Formula Help #40
I want to use excel to subtract two times and then divide a specified cell by the sum and return the value. Example: I get to point A @ 7:00am depart @ 9:00am and deliver 864 boxes. How many boxes per hour did I deliver? When I sum 9:00am - 7:00am I get 2:00 then if I divide 864 by that I get 0:00 if I leave it formatted as time. And if I change the format to general I get 10368?? I should get 432 9am-7am gives 0.00 in time. if you format as number it will give you 0.08333333... that means 0.083333333.... day. so 864/.0833333......./24 will give your per hour i.e. 432 A1 ------7:00am A...

How can I convert a formula to a value in excel?
I used to use lotus years ago and I know that it was /range value to change a formula to a value. Can you change a formula to a value in excel? Morgansmummy wrote: > I used to use lotus years ago and I know that it was /range value to change a > formula to a value. Can you change a formula to a value in excel? Copy, paste special-values. -- Registered Linux User no 240308 Fedora Core 4, Pan, Thunderbird and Firefox gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! ...

Including a formula in a path
Dear All, Is there a way of including a formula in a path. Every month I have to repeat the same calculations on different file eg: File name month 1, month 2,3,4 etc. I have to change the path name many times. I'd like to be able to say +c:\Month +a1 etc where a1 is the month number. The find and replace function doesn't work for a number of reasons. I tried concatenate but that involves fiddling around to get back to a path formula. Any other ideas? Many thanks Hi Peter =INDIRECT("C:\Month" & A1 & ".xls") HTH Cordially Pascal "Peter" <...

View actual page number
I have a document with several sections each beginnig with page 1. How do I view the actual page number? I just want to print a range but can't tell what the real number is. ...

Excel formula 04-12-10
I am trying to set up a spreadsheet to show the costs, after rebates, for a solar array. They rebate for New York is $1.75 per watt. That would be an easy formula of: =the number of watts*1.75, or =(C9*1000)*1.75 on my spreadsheet. However, they only reimburse a customer up to 5000 watts. what formula can I use to get excel to properly calculate this cell? Thanks for any help possible, Kyle Parenzan Hi Kyle =MAX(5000,(C9*1000))*1.75 -- Regards Roger Govier Kyle P. wrote: > I am trying to set up a spreadsheet to show the costs, after rebates, for a > solar ...