Return Zero If Calculation Result Negative
i have a sheet which adds up stock = SUM(F6D6) (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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Convert Calculation Result To Negative If Positive & Vice Versa
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.
View Replies!
View Related
If Formula Result Negative Then Return Zero
When i have a formula standing at A3 fore example =A1A2 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
View Replies!
View Related
Return Text Based On Calculation Result
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 "SemiCompact" 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 'Semicompact' or 'Slender'. How can i combine all the functions together?
View Replies!
View Related
Copy Values To Another Worksheet For Calculation & Return Result
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 nontechnical wording.
View Replies!
View Related
If Percentage Calculation Negative Show Zero
i 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)2580% i get the wrong answer i know there has to be a different formula Cost of visit= $113.00 Q=Your have a copayment 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.
View Replies!
View Related
Payment Function Giving Negative $ Result
I'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?
View Replies!
View Related
Calculation Result Is Displays As 0.0%
I 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),""))
View Replies!
View Related
Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result
I'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.
View Replies!
View Related
Calculation To Return 4 Values
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, 5065=average, 6575=good and <75% = Excellent with the font being bold, red, orange, yellow and green accordingly.
View Replies!
View Related
How To Return The Last Result In A List
I 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.
View Replies!
View Related
IF Function To Return No Data As A Result
I've created a Database using excel which feeds data to a pivot table I created based on it. My objective is to create a new item in this pivot table (Pivot Table > Formulas > Calculated Item) which calculates the average data for the past 6 months. The formula I used in order to get a result is this: =IF(((May+June+July+August+September+October)/6)=0;;(May+June+July+August+September+October)/6) and the problem is that the answer is 0 when there is no data for all 6 months (for example, there is a customer who hasn't baught anything during the past 6 months), the pivot table displays 0, instead of not showing this customer at all. My goal is for the IF function not to return any data if the result is 0, so that the pivot table doesn't show items with no data. I also tried: =IF(((May+June+July+August+September+October)/6)=0;"";(May+June+July+August+September+October)/6) and also using ;" "; but these formulas return #VALUE! in the pivot table, given that the Data is "SUM OF Sales" and "" is not a number. How can I get the formula not to show anything at all? In addition to this, I was wondering if it is possible to make this formula more automated, so that it calculates the average of the past 6 months, taking into account some type of reference month (rather than having to edit the formula each time a new month is analyzed).
View Replies!
View Related
Return A Result IF A Certain Value Is Found In Another Cell
I want to return a result IF a certain value is found in another cell. I've attached a sample workbook, but here's a description: On the "Inventory" worksheet, in column H, I want to lookup the quantity in column B from the "Warehouse Response" worksheet. If the Part # in column E of the response sheet is NI1088106, I want THAT value to show under the NI1088106 part number, and I want the quantities for the NI1089106 to show under that part number. Ideally, the result of the formula would show a "0" if no data is found. Here's the formula I tried beside the NI1088106 data, but it's not giving the desired results: =IF('Warehouse Response'!E:E="NI1088106", VLOOKUP(LEFT(A3,3),data,2,0))
View Replies!
View Related
Lookup With Multiple Result Return
I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
View Replies!
View Related
Return Result Based On Criteria
if Cell F42 contains 50%, then my fomula returns "wrong" =IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong"))))) how do I get the formula calculate correctly?
View Replies!
View Related
Lookup Value & Return Corresponding Result
I 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)
View Replies!
View Related
Return Subtotal Result To Variable
I seem to be having a little trouble with some of my code. It has worked previously without error. The variables CMvN and SkMvN are DIMed globally. The only thing that I recall that I have changed is that I have changed them from Double to Long. Changed it back to test and still experiencing problem. ' Filter named range using a named range on another sheet Range("FilterRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("NormalMetroAM"), Unique:=False CMvN = WorksheetFunction. Subtotal(101, Range("Cancelled")) SkMvN = WorksheetFunction.Subtotal(101, Range("Skipped")) When I put a break point in above it then enter: ? WorksheetFunction.Subtotal(101, Range("Cancelled")) in the immediate window, the correct value (e.g. 8.57142857142857E02) is returned. However, when I step through the code and go for example from CMvN line to SkMvN, the value isn't assigned to CMvN.
View Replies!
View Related
Lookup & Return Corresponding Result
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 Replies!
View Related
Return Result Of WorksheetFunction To Cell
I am editing macro formula... is it possible VB does calculation during code execution & return only the value to Cell. this way the formula calculation remain hidden & code will probably run faster..eg. Range("D3").Select ActiveCell.FormulaR1C1 = "= COUNTA(Data!C[3])1" Range("D3").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False I need to calculate & keep only value not formula in excel sheet each time the macro runs. I am getting error Worksheets("data").Range("D3").Value = Application.CountA(Data![C:C])  1
View Replies!
View Related
Return Result Based On Multiple Criteria
I have been trying this for hours but to no avail. I have a table with 4 columns headers Name, Amount, Loc and Code The name may look like ABC 1, ABC 2.... The Loc may be in US, GB... and the Code may be AA, BB I need to return a result "Y" if the sum of the amount is > 100 and "N" if the sum of the amount < 100 based on the conditions of the following :if 1) Name is the same entity, such as ABC 1 and ABC 2 and 2) Loc is the same, US..and 3) Code is the same I have attached a sample to illustrates the result
View Replies!
View Related
How To Return Blank Result If No Match For VLOOKUP
I'm using this formula (thanks to dafan & HalfAce [url] =IF(VLOOKUP($A177,$A$736:$C$787,3,FALSE)=0,"",VLOOKUP($A177,$A$736:$I$787,3,FALSE)) to return the value located in the 3rd column of the range when the value in the 1st column of the range matches the data in A177. However, there isn't a match for the data in A178 in the range, so the formula returns "#N/A". I would like the cell containing the formula to remain blank in those instances.
View Replies!
View Related
Return Result Based On Time In Cell
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time. If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs) If done at 0830 hrs, Shift 2 (0801 to 1600hrs) If done at 2030 hrs, Shift 3 (1601 to 2359 hrs) I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
View Replies!
View Related
Find Between Workbooks & Return Corresponding Result
I have two workbooks Raw File and Lookup_Test, I want to find the value of Column A from Raw File, in Column A of Lookup_Test, if the value if found then i want the value in Column D from Lookup_Test, to be copied to the Column B of Raw File . Well its like using vlookup , however i am trying to avoid using Vlookup and find any other way of doing the same.
View Replies!
View Related
Return Values Based On Lookup Result
I am currently looking at the workings of a spreadsheet designed by someone else. First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
View Replies!
View Related
Function To Accept 2 Criterias And Return Result
Name Day Total AlanMon400AlanThur600TomMon200TomWed300JamesMon1000Output4002001000 What function/formula can I use if I want that function to accept 2 criterias and returns me a result. Based on the above example, if the criterias are Alan & MOn, it will return 400 and if the criterias are Tom and Mon, it will return 200 and if the criterias are James and MOn, it will return 1000. Bascially, I want a function that can accepts 2 criterias. I do not want to sum up the total of the rows that are associated with one of the criteria i.e Alan. For Alan, I want 400 instead of 1000
View Replies!
View Related
Loop Return The Result Back In Row
Col ACol BCol CCol DCol ECol F row 1811TAX CONSIDERATIONS1TAX CONSIDERATIONS row 1911TAX CONSIDERATIONS#N/A row 2011TAX CONSIDERATIONS#N/A row 2121FLYING FALCON1TAX CONSIDERATIONS row 2221FLYING FALCON#N/A row 2321FLYING FALCON#N/A row 2431IN COMMAND1TAX CONSIDERATIONS row 2531IN COMMAND#N/A row 2631IN COMMAND#N/A I have 5 coulmns of data and in col F I have the following formula in row 18 =VLOOKUP(E18,$C$18:$D$800,2,FALSE) the results come back " TAX CONSIDERATIONS" however the same results comes back in row 21 and row 24......how can I modify this lookup to change when column B changes
View Replies!
View Related
Lookup Multiple Conditions To Return One Result
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing. So, If PRODUCT_A with OPTION_A, then PRICE = $X. If PRODUCT_A with OPTION_B, then PRICE = $Y. If PRODUCT_B with OPTION_C, then PRICE = $Z. etc. I want the user to select the product, then the option, and have the correct price "prefill" the cell.
View Replies!
View Related
Lookup & Return Adjacent Result
I have a list of people with SSN, about 3000. This list contains business done by each individual, which can be in all 50 states. I have another list of the same individuals with just their name and addresses and what we call "client number". I need to put the client number with the list for the individual states. See example list: List of individuals with client numbers 123 John Doe 456 Jane Doe List of individuals with state business John Doe AR John Doe CO John Doe FL John Doe MS John Doe TX Jane Doe MS Jane Doe TX Jane Doe AZ I need for those client numbers to appear in the column before the names on list with states. Example: 123 John Doe AR 123 John Doe CO 123 John Doe FL 456 Jane Doe MS 456 Jane Doe TX
View Replies!
View Related
Return Result & Formatting From Lookup
I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values  So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)
View Replies!
View Related
Return Result Based On Text In Cell
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities. In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario: Warranty Replacement = 20 Insurance Claim = 20 Billing Issues = 15 Retention Opportunities = 20
View Replies!
View Related
Compare Values In Multiple Columns And Return A Result
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value. I have a column between the years that calculates the percent of gross revenue for the specific department/cost. I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever. The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
View Replies!
View Related
Formula: Return Result Based On Other Cell Values
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this: if B5 is >3.01% then b6 =" Market Test Required" if B5 is +3% then b6 =110 if B5 is +2% then b6 =106 if B5 is +1% then b6 =103 if B5 is 0% then b6 =100 if B5 is 1% then b6 =96 if B5 is 2% then b6 =93 if B5 is 3% then b6 =90 if B5 is >3.01% then b6 ="Market Test Required" So on and so on.... I would really appreciate your help on this issue. I have used excel for some time now but not with complex formulas or any vb.
View Replies!
View Related
Lookup Value In Row & Return Result X Rows Down
I am working on a spreadsheet for some packages we are sending to multiple recipients. I have figured out how to get all the weights calculated, now my Mailing Manager has asked me to come up with a calculation for the actual postage amount for all the 1400+ packages...Unfortunately it is not as easy as that would seem. There are 9 different mailing zones and each zone has 19 weight categories and each category has a corresponding postage amount for the package. So I need to come up with a formula to calculate the package postage amount based on those multiple categories. For example: Zone 1: 01lb = $2.12 1.11.5lb = $2.12 1.62lb = $2.21 etc How do I go about entering in that information from which a formula can determine the proper package postage cost and what formula do I need to use in conjunction. This is all done within a address list so each package is a row and all weights and zones will be in a separate column. Package is in zone X and weighs Y pounds therefore the postage is Z. Something like that.
View Replies!
View Related
