# Return Zero If Calculation Result Negative

Apr 21, 2008i have a sheet which adds up stock = SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero.

View 3 Repliesi have a sheet which adds up stock = SUM(F6-D6) (told u it was basic) what i want to do is if a negative number is the answer i want the sheet to change it to zero.

View 3 RepliesADVERTISEMENT

I have a column of numbers such as

1001150

1001124

2224445

I need add a period in the following locations

10011.50

10011.24

22244.45

I figured this out using a format rule of

#.##

I then need to make the numbers negative so I did

-#.##

but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.

When i have a formula standing at A3 fore example =A1-A2

and when i fill in a number like 100 at A1

And i fill in a number like 150 at A2 then at A3 where i had make my formula in there stands -50

Now i want the -50 not to disapear but instead there must be stand a 0(zero)

What is the formula i can use

i dont mind to put in at another cel like A4 for example

I need an excel function for this...

Say,

Cell A2= 8

Cell A3= 1

If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?

I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.

In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.

I have a calculation in which the result is a negative number. Is there a way to return a zero if the result is negative?

View 13 Replies View RelatedI've got a file with sum formulas and datas as well,i need to know when ever i'm getting a negative no as result, it should be zero or the cell should be empty.

View 4 Replies View Relatedi am having a hard time in my microsoft excel class

for the life of me i cannot think of a formula to answer the question below

every time i type in =(c3)-25-80% i get the wrong answer

i know there has to be a different formula

Cost of visit= $113.00

Q=Your have a co-payment of $25.00 and the insurance will pay 80% of the remaining cost. Create a formula for the visit that will calculate the total amount you will have to pay.

How can i do this using Excel 2007. I have to cells, A1 and B1. A1 is always Positive whereas B1 might be Positive or Negative. I need a calculation/formula in C1 that ADDS cells B1 and A1 (B1+A1) IF B1 is Negative and SUBTRACTS A1 from B1 (A1-B1) IF B1 was Positive.

View 3 Replies View RelatedI want to subtract column B from column A and put the result in column C. If the result is a negative I want a formula to turn it to 0.

View 4 Replies View RelatedIf I put a formular in cell A2 "A1*.10" how can I get the result to come back negative if there are no negative numbers involved?

View 2 Replies View RelatedI'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-$630.13" instead of "$630.13."

Is this file corrupted or have I put something in incorrectly?

Am trying to get the sheetcalculate to be triggered and execute VBA code when a formula changes its result from positive to negative. The code works fine if I trigger it with a command button click. Auto Merged Post Until 24 Hrs Passes;

View 2 Replies View RelatedI am having a difficult time with a calculation. I took over someone else's duty who is no longer with the company. The calculation used did not account for entire columns & the data I need to pull fromhas grown larger than the calculation. When I hit "F2" to manually change the data array, everything works fine until I hit enter and then the result displays as 0.0%. When I hit the insert function button, the result shows as the correct number. I tried copying the format only from an adjacent cell, but this did not work. When I tried to undo my changes, the cell still displays as 0.0%

The original calc is:

=SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998=9,1,IF(cData!$N$2:$N$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$N$2:$N$45998<>"",1,0),""))

The new calc is:

=SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536=9,1,IF(cData!$N$2:$N$65536=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$65536="Internal",IF(cData!$N$2:$N$65536<>"",1,0),""))

The calc from the cell below the cell I am working in:

=SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998=9,1,IF(cData!$O$2:$O$45998=10,1,0)),0) )/SUM(IF(cData!$T$2:$T$45998="Internal",IF(cData!$O$2:$O$45998<>"",1,0),""))

I am doing some data analysis and need to figure out whether a number in column B is greater than a number in column A by £X.XX (Number to be determined) - is there a function, or custom function that would let me do this?

View 2 Replies View RelatedI'm quite a novice at Excel. I have a column of values that I sum as follows;

A

0

0

0

0

0

0 <----------------sum of A1:A5

A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.

I have an array formula which is working as expected returning the value of column K (offset by -1) if a number of criteria are matched.

[Code].....

The issue I'm having is there are occasionally duplicate values in column W and it's using the first value in its return, which isn't always correct.

Unfortunately due to the limited matches available the only way I can think to identify the correct value is by adding an additional criteria, which is that the correct value would always have a negative in Column X one cell above and to the right of where the value is being retrieved from. If there was no negative it would ignore this result.

Is there any way to add an additional criteria to the formula to stipulate that it must match a negative in column X but one cell above?

How would i wrte this formula correctly?

=IF(SUM(I5:J26))-(SUM(D5:E26))>=0,0,(SUM(I5:J26))-(SUM(D5:E26)

It needs to say if the sum of the two cell ranges is less than or equal to zero then display as zero, otherwise display the answer

I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.

Example:

A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5

A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell

A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10

I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.

I have a calculation that returns a result in F25 as a %. depending on the level of percentage I would like cell G25 to return >50% = poor, 50-65=average, 65-75=good and <75% = Excellent with the font being bold, red, orange, yellow and green accordingly.

View 11 Replies View RelatedI am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:

{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}

However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?

PRB

Y

Z

A90B90

A90B-90

[Code].....

an event macro to change the font colour of a cell whose value changes as a result of a calculation.

View 9 Replies View RelatedBeen working on this problem for a while, I need to have a vlookup (or other function) return locations based off of a concatenate (for unique identifiers). Only issue is that the concatenate formula creates doubles which returns the same location for each vlookup. I have attached an example file.

View 2 Replies View Relatedcreating a formula that will give me a result of either "W" or "L" depending on the amount in corresponding cell.

View 9 Replies View RelatedI need to figure out a formula that will look across a row and once it finds the criteria specified, it will return a value going down the corresponding column. I have attached a file showing the example.

View 3 Replies View RelatedI have this formula in C7:

=VLOOKUP(A7,A$20:C$76,3)

The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.

I have tried searching for this topic and have been unsuccessful. Perhaps my search was too broad. Either way, I am trying to understand how to search/query a specific data entry among several sheets, and then return corresponding values from adjacent cells.

View 3 Replies View RelatedI created a drop down menu from a named data range on a seperate spreadsheet in the same workbook. Based on the selection from that drop down menu I want a preassigned number to pull into another cell. I'm not thinking of the correct formula or something, because it's not working. Could it be because it's text? My spreadsheet with the data is as follows:

Bridger to American Fork 051725

Hunter to American Fork 051725

Hunter to Delta 051715

Hunter to Hiawatha 051728

Navajo to Am. Fork w/ front 051725

Navajo to Am. Fork w/o front 051725

The 1st column is the origination/destination and I named that range "freight hauls". Which is what the dropdown menu on another sheet (AP Reconcilliation) consists of. From that, I want the 2nd column number to pull into another cell on the AP Recon sheet. The formula I wrote that isn't working is: =index('AP Reconciliation'!A2:B8,match(A6,'Freight Hauls List'!B2:B7,0),2)

As an example, cell values as follows.

A1 = 1, 2 or 3, B1 = 2.5

Is it possible to say, if A1= 1 then make C1 equal to B1+4, otherwise return the value of 0?

I have in the attached file four variables, with their values in B3:B6, and the results of applying a formula to them in B8.

How to determine which value variable C must be changed to so that the formula returns the result in B10?

Solution.xlsxâ€Ž

Copyrights 2005-15 www.BigResource.com, All rights reserved