I have a spreadsheet where I am trying to locate negative values and add in the difference of the cells in Buffer A (Col C) and Buffer B (Col D).
I only want this function to return values that yield negative values (Examples of these are highlighted in orange. I don't want to yield positive values (Highlighted in blue).
I don't understand. There are negative numbers and positive numbers in blue. I also don't see any SUMIF formula in the sheet.
Can you explain what you want to show instead of a negative number? A zero? A blank?
You can use something like =if(yourformula = 0,"",yourformula) or =if(yourformula = 0,0,yourformula)
Or, if you want to convert all the negative numbers into positive numbers, you can wrap your formula into the ABS() function =ABS(yourformula)
trying to reverse-engineer your data, maybe this, starting in row 3 and copied down.
=IF(OR(COUNTIF(G3:L3,0)=6,COUNTIF(G3:L3,">"&0)),"",SUM(G3:L3)+(C2-D2))
The values that are highlighted Orange in 'Column M' are the values that I'm warranting. The formula in Cell 'M14' is "=I14+(C13-D13)". I14 is an already negative value and the difference between C13 and D13 added onto I14, yields a negative value (M14).
On the contrary, "M10" yields a postive value. The formula "=K10+(C9-D9)". The value is K10 is negative, but when the difference of C9 and D9 are added to K10, it yields a positive result. Which is what I don't want.
I only want this function to work on the cells that yield negative results.
try the formula in my second post.
thank you so much! This works great. However is there a way to only return only the negative values? I want the values highlighted in blue to stay as is. (IE: the value in L5 '-1' would become '14'. I would like it to stay as -1.)
Do you mean this?
=IF(OR(COUNTIF(G3:L3,0)=6,COUNTIF(G3:L3,">"&0)),"",IF(SUM(G3:L3)+(C2-D2)>0,SMALL(G3:L3,1),SUM(G3:L3)+(C2-D2)))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
That is exactly what I'm looking for. Thank you so much!!!
Last edited by AliGW; 02-14-2020 at 12:45 PM. Reason: Please don't quote unnecessarily!
How would I be able to apply this "=IF(OR(COUNTIF(G3:L3,0)=6,COUNTIF(G3:L3,">"&0)),"",IF(SUM(G3:L3)+(C2-D2)>0,SMALL(G3:L3,1),SUM(G3:L3)+(C2-D2)))" onto the values that are in C24:AP40. So instead of returning values onto the proceeding column, I would like to modify the values that are in C24:AP40.
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. As you are still relatively new here, I've added the link for you.)
https://www.mrexcel.com/board/thread...alues.1124257/
Please show what you mean in the workbook - you have added a whole load more data with no indication of what you want to see or where. What do you mean by "modify" exactly? You can't modify existing values with a formula.So instead of returning values onto the proceeding column, I would like to modify the values that are in C24:AP40.
Sorry for the misunderstanding. The new series of data is a different set of numbers, but I was wanting to know if I can modify these numbers instead of having the values return onto the proceeding column. I added a new spreadsheet where the values I'm referring to are highlightded.
Sorry, but I have absolutely no clue what this means:
The workbook still isn't clear - all you have done is added shading to the data you added to the workbook - it tells me nothing at all.I was wanting to know if I can modify these numbers instead of having the values return onto the proceeding column.
I just realized what you meant. So you cannot change the existing values with a formula.Originally Posted by AliGW
In another spreadsheet, the existing values "C24:AP40", had a formula incorporated onto the cells. I copied and pasted "text only". I was wanting to come up with the formula in this thread without having to combine both formulas.
Sorry - I still don't understand what you are trying to do.
I'm gonna start over. Sorry for the confusion.Sorry - I still don't understand what you are trying to do.
Going to start with a new spreadsheet, with different values. In Column F and G, you will see the formulas, "=IF(AND(B2>B3, B3<B4, B4<B5), D5-B4, 0)" and "=IF(AND(C2<C3, C3>C4, C4>C5), C4-D5, 0)", respectively. The values in Columns F and G are all based on the data from series 'A1:D141'. Attached you'll find a spreadsheet with the formulas I'm referring to.
I was wanting to incorporate a way to incorporate "=IF(OR(COUNTIF(G3:L3,0)=6,COUNTIF(G3:L3,">"&0)),"",IF(SUM(G3:L3)+(C2-D2)>0,SMALL(G3:L3,1),SUM(G3:L3)+(C2-D2)))" with the formulas "=IF(AND(B2>B3, B3<B4, B4<B5), D5-B4, 0)" and "=IF(AND(C2<C3, C3>C4, C4>C5), C4-D5, 0)".
I want the negative values in columns F and G to have the the previous rows difference added onto them and only return the values that stay negative. I've highlighted an example of what I want in light gold (The value of G122 should be -152.1, rather than -203.1. I've also highlighted an example of what I don't want in blue. ( The value of F133 should remain as -8.9. The value in I133 is just an example of a value that returns a postive value.) I only want the negative values.
I have a spreadsheet where I only want to return negative values. I'm using an if and statement, but it involves an absolute value for one of the conditions.
-Ex: (920-905) > |-35|.
15 > 35.
Returns false.
-Ex: (870-840) < |-35|.
30 < 35.
Returns true.
-Ex: (700-650) < |35|.
50 < 35.
Returns False.
This is what I've come up with. If the result turn out to be positive, I want the value to stay as it is.
-IF(AND((B2-C2) < |F3|, (B2-C2) + F3, "")
I only want to target cells that are negative (<0).
What is the purpose of the AND statement? It has no closing parenthesis.
Which of the cell references must be negative for the formula to calculate?
I am wanting to target cell values in a column that are negative. The cells would all be in Column "F". (Column "F" has varying values, negative and positive). I am also wanting to return values that are negative, exclusively.
So I'm trying to target values that meet these conditions:
-"<0"
-absolute value of cell in column "F" is greater than the difference of "B2-C2". (B2-C2) < |F3|
If these condition are both true, I want to add the difference of "B2-C2" to Cell F3.
If either/both are false, I want to leave the value alone.
-Ex: (870-860) < |-35|.
10 < 35.
Returns false.
Therefore. The value remains as -35.
Last edited by AliGW; 02-17-2020 at 08:01 PM. Reason: Please don’t quote unnecessarily.
Maybe this?
=IF(AND(F3<0,ABS(F3)>(B2-C2)),(B2-C2)+F3,0)
I'll give that a go. Thank you again!
Last edited by chkn89; 02-17-2020 at 10:10 PM.
Let us know how you get on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks