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=COUNTIF(F3:F38,"=VL") This works for totaling the VL in the cells in the column but what if I need it to add DVL in the same column to that total? So I need it to add both VL AND DVL as one total. I can make it add one or the other but not both
VL DVL
VL VL
VL
VL
VL
5 2
I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.
The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.
I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.
So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.
Here's a picture of my current spreadsheet : [URL] .....
Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.
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 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.
i 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 Replies View RelatedI have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.
View 5 Replies View RelatedI 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.
I have a spreadsheet for my local camera club that needs a few "IF" formulas that I have not a clue at how to accomplish. I have done the rest of the spreadsheet.
The formula needs to be
IF cell A1 = 22 or 23 or 24 (a manually entered number) then the answer = 1
IF cell A1 = 25 or 26 or 27 (a manually entered number) then the answer = 2
Also need another IF formula that adds up various cells: A1 + A2 + A3 + A4 and then adds the same cells on a 2nd and 3rd worksheet, and if the total is zero, then the cell will remain blank in the answer field, rather than having a zero in it.
I have a spreadsheet that uses IF(C5>J5,(EDATE(C5,12)),(EDATE(J5,12)))__ IF(J9<D9,(D9-$N$1)) where N1 is current date. If C5 and J5 are empty how do I get it to ignore the formula and just leave the other cells blank?
View 3 Replies View RelatedI have written a macro that copies a row to a new sheet and adds a formula to that copied row.
View 8 Replies View RelatedI entered exactly 113,876.92 in cell L16 I entered exactly 113,390.02 in cell L17 I entered =L16-L17 in cell L18 L18 incorrectly shows the result at 486.9000000000009000 (note the extra "9" after the 11 zeros). When I expand the viewable digits on L16 and L17, they have ALL zeros after the cents. (I went out at least 25 digits). I can't be the first one encountering this.
View 4 Replies View RelatedIn Cell (W6) I have a value for example of 100,000 In Cell (AE6) will have a value of for example -100,000. In Cell (AF) I want to put a formula that says "if the number in (W6) is a positive number and the number in (AE6) is a negative number the return a value of "1". and vice versa if the number in cell (W6) is a negative number (-100,000) and number in Cell (AE6) is s positive (25,000) then also return a value of "1".
View 9 Replies View RelatedI want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
I can't use 1904. I have a formula that almost works.
I am doing everything in Military time. This is the finial peice of the puzzle.
Our shift begins at 18:00 and ends up until 7:00 (am) or so depending onwork
A1 I have a time (Projected-Estimate of when all work should be done)
A2 I have a time (Actual- Time the work was actually complete.)
A3 Will be show the difference in either a Negative or Positive time.
Three Examples: (The first two work as is and are perfect) ....
IF formula
=IF(D9="","",IF(AND(G9>0,G10>0),"",IF(G9>=G10,G9,G10))+IF(E10=0,"",E10))
G9 and G10 can be positive or negative. If G9 or G10 is a negative number,
I want the greater of the two to be used and then to be added to E10. But
right now, Excel is saying -20 (which is G10) is greater than zero. I'm
getting a VALUE error.
Possibly to combine If statement and lookup stmt. I have a spreadsheet that calculates principle, interest, and combined pmt amount based on certain amortization and payment frequency. In one cell I would like the expiry date to pull in (the expiry date would be the date that the fund balance would be at zero ie. all payments have been made and principle is nil). I am trying to make a formula that will search down the payment column and when it sees a negative number or zero, it will fill in the expiry date (which is in a second column) that is attached to that figure.
This may be more of a math question rather than an Excel one.
If I have a spreadsheet with a cell that produces different negative number each time it is used, is there a way i can add a formula to make it a positive number.
So if Cell A1 showed -£244.22, I want to add an equation that changes if to £244.22
Cell A1 changes so it would need to do the same for any negative number within that cell.
(First please don't send me a link on how to subtract time or show Negative time. I know about 1904 ect.. I can't use that. I need a variation on the formula I am using if possible)
A1 I have a time
A2 I have a time
A3 I have a formula subtracting A2 from A1
The times will vary in A1 & A2. Sometimes A1 will be earlier or later thus giving me negative or positive times. I have a formula that will give me a correct answer but will always show either Positive or Neg depending on what I put in the last part of the formula "-H:MM or H::MM" =TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM") in A3. I want it to do the subtraction but say if A1 is > than A2 then "-H::mm", If A1 is < A2 then "h::mm". I don't know if that is possibly or how to incorporate that into what I have.
fix my (if formula) so it includes data from sheet 2. Sunday and monday values change but every other remains the same. I was given this formula and cant seem to add to it. Also is there a way to show only what i need instead of negative and 0 balances?
View 2 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 RelatedIgnoring Negative results in a formula
Question withdrawn
the Times work fine 22:00pm to 5:30am or 6:00am to 5:30am
Formulas
J4
=IF(ISNUMBER(D4),(D4>E4)*MEDIAN(0,E4-N$4,O$4-N$4)+MAX(0,MIN(O$4,E4+(D4>E4))-MAX(N$4,D4))-F4-L4,0)
K4
=IF(ISNUMBER(D4),MAX(0,MOD(E4-D4,1)-J4-L4-M4-I4-F4),0)
L4
=IF(ISNUMBER(D4),IF(MOD(E4-D4,1)-F4>P$4,(MOD(D4+P$4,1)>E4)*MEDIAN(0,E4-N$4,O$4-N$4)+MAX(0,MIN(O$4,E4+(MOD(D4+P$4,1)>E4))-MAX(N$4,MOD(D4+P$4,1))),0),0)
M4
=IF(ISNUMBER(D4),MAX(MOD(E4-D4,1)-F4-I4-L4-P$4,0),0)
but if I try 22:00pm to 6:00am or later
The following is displayed
Negative Time in J4 and the wrong result in K4
this is not what I need
It should be showing the following
J4 2:00 K4 5:00 L4 00:00 M4 00:00
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 have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?
I was trying if(right(A1,1)="-",TBD,A1)
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?
View 7 Replies View RelatedI can't seem to find the right formula that can tell me how many times i have a negative number in a series of cells.
View 1 Replies View RelatedI have several thousand values to compare, using = Exact(A2, B2) to find exact matches and return TRUE/FALSE. I've gotten a bunch of incorrect results especially where both values equal zero.
Data attached : Exact_values.xlsx‎
I am looking for a formula to do the following:
In Tab 1, I have a negative number and the word "Original" next to it. In Tab 2, I have a mix of positive & negative numbers. I want all numbers that are negative to display the word "original" and all positive to display" new." How do I do that? Also, I want the opposite to work as well-- if Tab 1 has a positive number, I want all positive numbers in Tab 2 to display "original."