Sum formula in E5, add in same cell formula for gain/loss?

[StargateFan]

[Hallelujah! How many times did I wonder how we were going to survive
without MS ngs and no-one, no-one told me I could still access the ngs
through servers _other_ than the now defunct MS one <lol>! Ngs via
newsreader are just so much easier, so really glad to be back here!]

I've been having difficulties this morning. First off, I have an
income in E2 of $2,029.12 and in E4 of $1,765.28. When subtracted
with formula in E5 of =SUM(E2-E4), I get $263.84, which numerically is
fine. However, since it's income, this $263.84 actually represents a
loss so it should be showing as a negative.

Then, in E6 formatted as percentage, I figured out that =(E4-E2)/E2
shows the difference in those amounts as a percentage, -13%, which is
perfect.

My question is how do I get output in E5 of this type (in red), which
actually combines both and also correctly shows as negative or
positive income gain/loss:

-$263.8 (-13% income loss/or gain as case may be)

Thanks so much. Really appreciate any help. D

 

[Jim Cone]

Don't use the Sum function in E5...
Use "=E4-E2" and the custom number format: #,##0.00;[Red]-#,##0.00
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"StargateFan" <IDon'(e-mail address removed)>
wrote in message
news:[email protected]...

 

[StargateFan]

Don't use the Sum function in E5...
Use "=E4-E2" and the custom number format: #,##0.00;[Red]-#,##0.00
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)

OMG, thanks Jim! It looks like I'll have to rid myself of a lifetime
of a bad habit! I always use SUM! Okay, it's out the window from
here on out <lol>.

I actually changed it to currency. I went to see what the other ones
were because there seemed to be only brackets with the red. Glad to
see it was so easy to change (didn't think of that). I formatted as
$#,##0.00;[Red]-$#,##0.00 which I then saw turned it into currency
with minus red in the currency style instead of custom style! D'uh!
<lol>

Thanks, re the first part of the formula question. That'll work if
the cell _only_ contained the currency. I'm wondering what will
happen when we add the percentage in brackets to the same cell so that
I can keep this spreadsheet nice and tidy. Otherwise, I'll have to
have an extra 4 columns to deal with percentages alone. Nicer to have
it all contained.

But at least I learned to think a different way and get the right
results in the addition. Bah. Stupid linearly-thinking humans,

"StargateFan" <IDon'(e-mail address removed)>
wrote in message

[Hallelujah! How many times did I wonder how we were going to survive
without MS ngs and no-one, no-one told me I could still access the ngs
through servers _other_ than the now defunct MS one <lol>! Ngs via
newsreader are just so much easier, so really glad to be back here!]

I've been having difficulties this morning. First off, I have an
income in E2 of $2,029.12 and in E4 of $1,765.28. When subtracted
with formula in E5 of =SUM(E2-E4), I get $263.84, which numerically is
fine. However, since it's income, this $263.84 actually represents a
loss so it should be showing as a negative.

Then, in E6 formatted as percentage, I figured out that =(E4-E2)/E2
shows the difference in those amounts as a percentage, -13%, which is
perfect.

My question is how do I get output in E5 of this type (in red), which
actually combines both and also correctly shows as negative or
positive income gain/loss:

-$263.8 (-13% income loss/or gain as case may be)

Thanks so much. Really appreciate any help. D

 

[StargateFan]

On Thu, 20 Jan 2011 12:10:38 -0500, StargateFan

[snip]

[snip] ... First off, I have an
income in E2 of $2,029.12 and in E4 of $1,765.28. When subtracted
with formula in E5 of =SUM(E2-E4), I get $263.84, which numerically is
fine. However, since it's income, this $263.84 actually represents a
loss so it should be showing as a negative.

Then, in E6 formatted as percentage, I figured out that =(E4-E2)/E2
shows the difference in those amounts as a percentage, -13%, which is
perfect.

My question is how do I get output in E5 of this type (in red), which
actually combines both and also correctly shows as negative or
positive income gain/loss:

-$263.8 (-13% income loss/or gain as case may be)

[snip]

Just for future reference, after seeing what Jim Cone advised, I
played around with the separate percentage cell to see if I could get
different outputs depending on whether gain or loss and this
approached the right output:

=(E4-E2)/E2
with custom formatting of:
0%;[Red]-0%" income loss"

Unlike when using something similar in currency, it didn't go to black
when a positive number, but the text did disappear with a positive
number.

Anyway, good for adding text to the output. This doesn't deal with
putting both the gain/loss in currency along with gain/loss in
percentage in same cell but good for future reference.

 

[Pete_UK]

If you do put the gain/loss in currency in the same cell as the gain/
loss percentage, eg like:

-$263.80 (-13%)

then this combination would be a text value, and your custom
formatting will have no effect on it. So, you need to decide what
would be more important - separate cells with custom formatting or
combined cells all in black.

Hope this helps.

Pete

On Thu, 20 Jan 2011 12:10:38 -0500, StargateFan


[snip]

[snip] ... First off, I have an
income in E2 of $2,029.12 and in E4 of $1,765.28.  When subtracted
with formula in E5 of =SUM(E2-E4), I get $263.84, which numerically is
fine.  However, since it's income, this $263.84 actually represents a
loss so it should be showing as a negative.

Then, in E6 formatted as percentage, I figured out that =(E4-E2)/E2
shows the difference in those amounts as a percentage, -13%, which is
perfect.

My question is how do I get output in E5 of this type (in red), which
actually combines both and also correctly shows as negative or
positive income gain/loss:

-$263.8 (-13% income loss/or gain as case may be)

[snip]

Just for future reference, after seeing what Jim Cone advised, I
played around with the separate percentage cell to see if I could get
different outputs depending on whether gain or loss and this
approached the right output:

=(E4-E2)/E2
with custom formatting of:
0%;[Red]-0%" income loss"

Unlike when using something similar in currency, it didn't go to black
when a positive number, but the text did disappear with a positive
number.

Anyway, good for adding text to the output.  This doesn't deal with
putting both the gain/loss in currency along with gain/loss in
percentage in same cell but good for future reference.

 

[Vacuum Sealed]

You could combine 2 seperate cells to show a representation of it though.

For instance: Assumed

A1 = $OldSalaryValue
B1 = $NewSalryValue
C1 = ValueDiff (=$B$1-$A$1)
D1 = %Diff (=($C$1/$A$1)*100 [NumberFormat])

E1 = C1&" ("&D1&"%)"

Which will give you:

-$263.80 (-13%)

Again, the only drawback here is that this is a text representation only of
the 2 combined cells.

HTH
Mick

 

[Vacuum Sealed]

After a little more mucking around I have discovered the only negative
consideration in using something like this is that you're kinda restricted
to how it displays.

eg

In actuality the cell would display as thus:

E1 = C1&" ("&D1&"%)"

-263.28 (-13.0026809651475%)

Someone else may be able to weigh in with something like a Right(), Left, or
Trim() to incorporate into it to remove any excess numbers to trim it to
just representing the whole number or at least to 2 decimals.

HTH
Mick

 

[Pete_UK]

Mick,

you would use TEXT with a format string to control how the digits are
displayed.

Hope this helps.

Pete

 

[Vacuum Sealed]

Thx for the pointer there Pete.


So with
A1 = $OldSalaryValue
B1 = $NewSalryValue
C1 = ValueDiff (=$B$1-$A$1)
D1 = %Diff (=$C$1/$A$1 [%Format])

the resulting formula of

=C1&" ("&TEXT(D1,"0%")&" )"

will give you

-$263.80 (-13%)

HTH
Mick

 

[Pete_UK]

You might also want to put a TEXT around the C1, so that you can
ensure currency with 2 dp.

Pete