Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "Stephane Quenson" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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. "Stephane Quenson" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "squenson" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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. "squenson" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "squenson" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "Stephane Quenson" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "squenson" wrote: 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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? -- David Biddulph "Mark" wrote in message ... 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 "squenson" wrote: 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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Plus or minus help
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 "David Biddulph" wrote: 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? -- David Biddulph "Mark" wrote in message ... 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 "squenson" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Plus or Minus | Excel Worksheet Functions | |||
Plus or Minus | Excel Discussion (Misc queries) | |||
How do you go one whole row minus another whole row | Charts and Charting in Excel | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
Value Minus Value = ??? | Excel Discussion (Misc queries) |