Plus or minus help

[Guest]

In my formula I would like:

column M4:M100 to be the +/- of the numbers down I4:I100
Column N4:N100 to be the +/- of the numbers down J4:J100
Column O4:O100 to be the +/- of the numbers down K4:K100

Is this formula or code?

Can u help.

If it is formula ok.. If it is code, where doe s it fit in this?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub


THANKS mark

 

[Guest]

I am not sure that I understand the question, but let me try answering it
anyway

If I4 contains 25, you want M4 to contain -25, if I4 contains -25, you want
M4 to contains 25. In that case, put =-I4 in cell M4.

If I4 contains 25, you want M4 to contains -25, if I4 contains -25 you also
want M4 to contains -25. Then the formula is =-ABS(I4)

Let us/me know if this is fine or not.

Stephane.

 

[Guest]

Stephanie,

Thanks...Not what I tried to say

If I4 is 23 and L4 is 20 then M4 is -3
If I5 is 18 and L4 is 12 then M4 is 6 or +6
If I100 is 25 and L100 is 17 M4 is 8 0r +8

M column is + or - the difference between L column which is the # and I
column the new #.

IF J4 is 18 and L4 is 15 then N4 is -3

If K4 is 25 and L4 is 10 then O4 is 15 or +15

3 columns revolving around the number in L4:100 those being I,J,K
respectively and being reported respectively in M,N,O as (+ or -)

Thank you.

Mark

 

[Guest]

Stephanie, I messed this up, mostly because I have made a change.. Please let
me restate:


I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.

L is the target L4:100

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

AS I4:I100 gets a new #, L changes. (it's and average)

Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.

 

[squenson]

Let's do it step by step:

You want:
ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

Could we say that M4 is equal to L4 minus I4? In that case, write in cell M4
=L4-I4, and copy this cell down, the formula will adapt automatically on each
row.

 

[Guest]

Sqeq.
Yes that works however I need to show a neg # in M if I4 is less than L4.

with this I just shoe the difference not + or - or whole or neg.
That is step one.

Mark

 

[Guest]

Actually, Yes this works, but it is =I4-L4

That works.

Mark

Next step to move M to N , N to O O to trash as a new # hits I

we will assume 4:100

Thank you.

 

[Guest]

No I am not getting a true number for some reason in M.Instead of 2 i am
gettinf 1.7 when the difference is actually 2.0 .that baffles me.

even tried =sum(I4-L4)
L4 is 16 I4 is 18.. I get 1.7 hmmmmm

Could my code be effecting that:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

 

[Guest]

no..i feel horrible now.

M4 (assuming M4:M100) is I4-L4

For sum reaso I am getting a decimal 1.7 in there when I4 is 14 and L4 is
16. should be 2 or 2.0
L5 is 18, I5 is 15 should be -3 I am getting -2.7

The math is off.
I think my code(which has some averages in there(which makes L) is messing
this up:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

 

[squenson]

Excel is rarely wrong with basic operations. Could it be that L4 or I4 are
not exactly equal to 16 and 18? Could there be decimals, but they are not
shown? Try Format > Cells > Number

And no, your code cannot influence how excel performs a subtraction.

 

[Guest]

You are correct.

I see part of the problem:

L is an average # of I,J,K so since I am actually getting 16 because I
reduced the decimals (from the toolbar)the number in there is actually
something like 16.4 so M gives me the actual difference like -1.7 or 2.4.

Can I round L?
Is that code?
Then I guess I need to round M.

I'm sorry, Thank you.

Mark

 

[David Biddulph]

You still haven't explained when you want +ve and when you want -ve.

You say "I need to show a neg # in M if I4 is less than L4", but you had
previously said:
"If I4 is 23 and L4 is 20 then M4 is -3
If I5 is 18 and L4 is 12 then M4 is 6 or +6
If I100 is 25 and L100 is 17 M4 is 8 0r +8"

You had 3 cases where I was greater than L, but for one case you wanted
a -ve result and for the other two cases you wanted +ve.

Can you explain?

 

[Guest]

David,
Thank you,
Of course I typed what I meant incorrectly. You were absolutely correct.
It is:
=(I4-L4) regardless of my example:

With Bernies expertise I have gone on. I have one of thos B_LL Breaker
headaches.
My next problem was this:

My present code which now includes a running history of the +/- of L in
column M,N,O or (M,N,O) is a running history of the +/- of L.

Predicament.

If L is the target, and it reads a score of 20 and I score 16 M should read
-4
However, If I enter todays score of (16) L's average changes to 19
(tomorrows target) and M reads -3. which is wrong.
because of the entry.

M should read: -4
(Based on the 20 that was in L) just before I made a new L

ie:-4, 8,-2
Then based on 19(L) and a score of 24(I) M,N,O should be
5 -4 8

any other thouhts on M.?

I'm dead in the water.

Mark