Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Negative value to positive
This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) copys (to the cell it is written to) the sum of negative dollar amounts from another sheet. I need to add to this formula, a formula that changes the negative dollar amount total to a positive. I know that the abs function converts, but i'm not sure how to incorporate it into this formula. Please help. Thanks, Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
Hi Mark,
Am Mon, 24 Jun 2013 19:53:10 +0100 schrieb Mark74w1: This formula, =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) copys (to the cell it is written to) the sum of negative dollar amounts from another sheet. I need to add to this formula, a formula that changes the negative dollar amount total to a positive. I know that the abs function converts, but i'm not sure how to incorporate it into this formula. try: =ABS(SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
"Mark74w1" wrote:
This formula, =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) copys (to the cell it is written to) the sum of negative dollar amounts from another sheet. I need to add to this formula, a formula that changes the negative dollar amount total to a positive. I know that the abs function converts, but i'm not sure how to incorporate it into this formula. Use the ABS function if the SUMIF result might be negative __sometimes__, positive other times. However, if the SUMIF result is __always__ negative ("the sum of negative amounts"), you can simply negate it, to wit: =-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) |
#4
|
|||
|
|||
Quote:
Thank you for you help with this formula. Obviously I'm not quite there yet with understanding the formatting of formulas. I would sure like to know if there was a simple rule of thumb for this. Thanks, Mark |
#5
|
|||
|
|||
Quote:
Thank you for the response on this problem. For some reason, when I entered the corrected formula it opens my documents looking for a file. The reply by Mr. Busch worked, however I still want to find out why this formula didn't work so that I could use it as well. Thanks, Mark |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
"Mark74w1" wrote:
'joeu2004 Wrote: "Mark74w1" wrote: This formula, =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) [....] you can simply negate it, to wit: =-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) Joe, Thank you for the response on this problem. For some reason, when I entered the corrected formula it opens my documents looking for a file. The reply by Mr. Busch worked, however I still want to find out why this formula didn't work so that I could use it as well. I had copy-and-pasted your posted formula exactly as you had written it, not realizing it has a typo: the left-parenthesis before the word "Cost" on the right. Claus corrected your typo without drawing your attention to it. Modifying Claus's formula: =-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
On Tue, 25 Jun 2013 15:10:20 +0100, Mark74w1
wrote: 'joeu2004[_2_ Wrote: ;1612463']"Mark74w1" wrote:- This formula, =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) copys (to the cell it is written to) the sum of negative dollar amounts from another sheet. I need to add to this formula, a formula that changes the negative dollar amount total to a positive. I know that the abs function converts, but i'm not sure how to incorporate it into this formula.- Use the ABS function if the SUMIF result might be negative __sometimes__, positive other times. However, if the SUMIF result is __always__ negative ("the sum of negative amounts"), you can simply negate it, to wit: =-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500) Joe, Thank you for the response on this problem. For some reason, when I entered the corrected formula it opens my documents looking for a file. The reply by Mr. Busch worked, however I still want to find out why this formula didn't work so that I could use it as well. Thanks, Mark Cell formatting negated it being seen as a formula? |
#8
|
|||
|
|||
Quote:
Thank you again for the help. I didn't see that either. Can I ask you one more question? I've tried to make this work with no success. =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payrol l'!T8:U1500). The purpose of this formula is to sometimes add the two, or to have one entered if the other is blank. Also can you, if payroll is "t" and cost plus is "p" then total the two. I hope I'm not taking advantage of your expertise, but these two solutions could greatly simplify my 9 page spread sheet. Thanks Mark |
#9
|
|||
|
|||
Quote:
Thanks for interest in helping me. I'm starting to get the hang of this but there are so many tricks that I need to learn. Thanks again, Mark |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
"Mark74w1" wrote:
I've tried to make this work with no success. =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payrol l'!T8:U1500). The purpose of this formula is to sometimes add the two, or to have one entered if the other is blank. Also can you, if payroll is "t" and cost plus is "p" then total the two. I'm not sure I understand the requirement(s?). First, I assume that 'cost plus invoice'!T8:U1500 is still negative, and you want the sum to be positive. But I assume that payroll!T8:U1500 is positive. If you want to sum 'cost plus invoice'!T8:U1500 when O8:O1500 is "p" and sum payroll!T8:U1500 when O8:O1500 is "t", then add the two sums, that would be: =SUMIF(payroll!O8:O1500,"t",payroll!T8:U1500) - SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500) If you want to sum the two only when both conditions are met in corresponding rows, that would be: =SUMPRODUCT(('Cost Plus Invoice'!O8:O1500="p")*(payroll!O8:O1500="t"), payroll!T8:U1500 - 'Cost Plus Invoice'!T8:U1500) Please correct my interpretation of the requirements, if it is wrong. Perhaps a concrete example that shows when to add and not add the two would clarify any misunderstanding. |
#11
|
|||
|
|||
Quote:
Can you help me again? =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus Invoice'!T$8:U$1510) Does not work: this means if troy or Mark or Robert pays the bill. Also: =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t" when 'Cost plus invoice!m$8:m1500,"p",'Cost Plus Invoice'!T$8:U$1510) This means if o is "t" and "m" is p, then sum. These two formulas would simplify my 9 page spread sheet. Thanks Mark |
#12
|
|||
|
|||
Quote:
Can you help me again? =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus Invoice'!T$8:U$1510) Does not work: this means if troy or Mark or Robert pays the bill. Also: =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t" when 'Cost plus invoice!m$8:m1500,"p",'Cost Plus Invoice'!T$8:U$1510) This means if o is "t" and "m" is p, then sum. These two formulas would simplify my 9 page spread sheet. Thanks Mark |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
Hi Mark,
Am Thu, 27 Jun 2013 18:04:45 +0100 schrieb Mark74w1: =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus Invoice'!T$8:U$1510) try: =SUMPRODUCT(((O8:O1500="t")+(O8:O1500="m")+(O8:O15 00="r"))*T8:U1500) =SUMIF('Cost Plus Invoice'!O$8:O$1510,"t" when 'Cost plus invoice!m$8:m1500,"p",'Cost Plus Invoice'!T$8:U$1510) This means if o is "t" and "m" is p, then sum. try: =SUMPRODUCT(--(M8:M1500="p"),--(O8:O1500="t"),T8:U1500) Regards Claus B -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
Hi Mark,
Am Thu, 27 Jun 2013 20:04:37 +0200 schrieb Claus Busch: =SUMPRODUCT(--(M8:M1500="p"),--(O8:O1500="t"),T8:U1500) there is a typo above. Try: =SUMPRODUCT((M8:M1500="p")*(O8:O1500="t")*(T8:T150 0+U8:U1500)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
Hi Mark,
Am Thu, 27 Jun 2013 20:09:13 +0200 schrieb Claus Busch: =SUMPRODUCT((M8:M1500="p")*(O8:O1500="t")*(T8:T150 0+U8:U1500)) sorry, I forgot the sheet name =SUMPRODUCT((('Cost Plus Invoice'!O8:O1500="t")+('Cost Plus Invoice'!O8:O1500="m")+('Cost Plus Invoice'!O8:O1500="r"))*'Cost Plus Invoice'!T8:U1500) =SUMPRODUCT(('Cost Plus Invoice'!M8:M1500="p")*('Cost Plus Invoice'!O8:O1500="t")*('Cost Plus Invoice'!T8:T1500+'Cost Plus Invoice'!U8:U1500)) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#16
|
|||
|
|||
Quote:
I'll explain. =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500) And, or ('payroll'!O8:O1500,"t",'payroll'!T8:U1500). Joe, My spread sheet has 9 sheets. Home, Takeoff, Phase totals, Cost entry, Cost plus invoice w/ phase totals, Payroll, Employee info and Profit and loss all hyperlinked to each other. The profit and loss sheet has to share totals from two different sources in the same cell. sometimes I'll use the totals from the fixed contract side (takeoff, cost entry, phases and profit & Loss) and sometimes I use the Cost plus side (Cost plus invoice, phases & Profit and loss). The purpose of this formula is to sometimes add the two totals to a single cell or to just have one entered if the other sheet has no "p" or no "t" Etc. The other condition would be to only get the total if (payroll or any other sheet I choose has the criteria that a cell has (for example)"t" in one cell and a "p" in another cell (That both cells must have a letter in them or nothing will totaled in the cell with the =sumif formula. It's so hard to explain on print when a verbal would do it in a secant. Maybe I should find a way for you to play around with the entire work book. Thanks, Mark |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Negative value to positive
"Mark74w1" wroteL: 'joeu2004 Wrote: =SUMIF(payroll!O8:O1500,"t",payroll!T8:U1500) - SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500) [....] =SUMPRODUCT(('Cost Plus Invoice'!O8:O1500="p")*(payroll!O8:O1500="t"), payroll!T8:U1500 - 'Cost Plus Invoice'!T8:U1500) [....] The purpose of this formula is to sometimes add the two totals to a single cell or to just have one entered if the other sheet has no "p" or no "t" Etc. The other condition would be to only get the total if (payroll or any other sheet I choose has the criteria that a cell has (for example)"t" in one cell and a "p" in another cell (That both cells must have a letter in them or nothing will totaled in the cell with the =sumif formula. And I believe that is exactly what those two formulas do. If you have examples where one or the other formula does not produce expected results, please post the circumstances (values) of those examples. However, perhaps the issue is the idea of "add". As I noted previously, I assume the same conditions and requirements as your previous postings in this thread, to wit: the 'cost plus invoice'!T8:U1500 values are negative, and you want to add their absolute value. This can be accomplished using -SUMIF(...). So in effect, the SUMIF formula above could be written =SUMIF(...) + -SUMIF(...). But that can be simplified to =SUMIF(...) - SUMIF(...). Similarly for the SUMPRODUCT expressions. If you are applying the formulas above to circumstances involving only positive values, change the minus ("-") to plus ("+"). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting positive amts from negative and positive from positive | Excel Worksheet Functions | |||
Positive and negative graph | Charts and Charting in Excel | |||
Some Positive, Some Negative | Excel Worksheet Functions | |||
IF positive/If negative???? | Excel Worksheet Functions | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) |