Excel problem

Discussion in 'Computer Information' started by Robert Baer, Feb 12, 2014.

  1. Robert Baer

    Robert Baer Guest

    I have an Excel spreadsheet with dates and prices of Gold since Dec
    9, each older date, price below more recent one (don't ask).

    Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    buy10 in F=IF(C3>D3,"buy","sell"),
    amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    Total of J negative; SAME amount if buy/sell IF switched ("<"
    instead of ">").
    Why?
     
    Robert Baer, Feb 12, 2014
    #1
    1. Advertising

  2. "Robert Baer" <> wrote in message
    news:zTTKu.327155$...
    > I have an Excel spreadsheet with dates and prices of Gold since Dec 9,
    > each older date, price below more recent one (don't ask).
    >
    > Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    > buy10 in F=IF(C3>D3,"buy","sell"),
    > amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    > Total of J negative; SAME amount if buy/sell IF switched ("<"
    > instead of ">").
    > Why?
    >
    >


    Shouldn't you be averaging the price (column B)?

    C = ave(b<start>:b<end>) (B3:B12) using your example of the code you have.

    How are you populating the cells in column D. You call an IF for the value
    of C and D, and I wonder where you filled D because it is not defined in
    your formula. You have several cells, columns of cells, that you are
    calling in your code, there is no indication that they hold any values, or
    where the values come from.

    It looks like you are either pulling data, or manually entering it, and you
    have the date (coilumn A) and the price (column B). It seems to me that you
    should be averaging the 10-day price based on the contents of column B, and
    putting the result in column C. For example A1 is ten days ago, B1 is the
    closing price on that day. A10 is todays date, B10 is todays price, C10 is
    the average of B1:B10. Tomorrow, you are averaging B2:B11 and putting the
    result in C11. (I have not figured out what cells are inclusive to ten days,
    but you should see the pattern here.)

    What you get building up in column C is the running average of the ten
    previous days of column B. Then you set the floor and ceiling of the value
    of C and either buy more or sell what you have to limit your loss.

    Seems to me that eTrade and Schawb already have tools like this. You should
    not have to write this from scratch, although I surely understand
    thrill-of-the-battle in doing so.
     
    Jeff Strickland, Feb 13, 2014
    #2
    1. Advertising

  3. Robert Baer

    Robert Baer Guest

    Jeff Strickland wrote:
    >
    > "Robert Baer" <> wrote in message
    > news:zTTKu.327155$...
    >> I have an Excel spreadsheet with dates and prices of Gold since Dec 9,
    >> each older date, price below more recent one (don't ask).
    >>
    >> Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    >> buy10 in F=IF(C3>D3,"buy","sell"),
    >> amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    >> Total of J negative; SAME amount if buy/sell IF switched ("<"
    >> instead of ">").
    >> Why?
    >>
    >>

    >
    > Shouldn't you be averaging the price (column B)?

    * Goofed in write-up here: C=AVERAGE(B3:B12).
    D=AVERAGE(B3:B22) for 20 days

    >
    > C = ave(b<start>:b<end>) (B3:B12) using your example of the code you have.
    >
    > How are you populating the cells in column D. You call an IF for the
    > value of C and D, and I wonder where you filled D because it is not
    > defined in your formula. You have several cells, columns of cells, that
    > you are calling in your code, there is no indication that they hold any
    > values, or where the values come from.

    *
    >
    > It looks like you are either pulling data, or manually entering it, and
    > you have the date (coilumn A) and the price (column B). It seems to me
    > that you should be averaging the 10-day price based on the contents of
    > column B, and putting the result in column C. For example A1 is ten days
    > ago, B1 is the closing price on that day. A10 is todays date, B10 is
    > todays price, C10 is the average of B1:B10. Tomorrow, you are averaging
    > B2:B11 and putting the result in C11. (I have not figured out what cells
    > are inclusive to ten days, but you should see the pattern here.)
    >
    > What you get building up in column C is the running average of the ten
    > previous days of column B. Then you set the floor and ceiling of the
    > value of C and either buy more or sell what you have to limit your loss.
    >
    > Seems to me that eTrade and Schawb already have tools like this. You
    > should not have to write this from scratch, although I surely understand
    > thrill-of-the-battle in doing so.
    >

    For more complete info:
    E=IF(B3>C3,"buy","sell") for buy10
    F=IF(E3<>E4,B3,F4) for amount10
    G=IF(B3>D3,"buy","sell") for buy20
    H=IF(G3<>G4,B3,H4) for amount20
    I=(F3-F4) for gain10
    J=(H3-H4) for gain20.
    Current data "on top" or row 3,oldest data (so far) "on bottom" or
    row 542 (12/9/2011).
    Easy as all heck to add older data in spare time.
    K=SUM(I3:I528) for Total10
    L=SUM(J3:J528) for Total20.
    K and L are negative, and the numbers DO NOT change if i change the
    ">" in formula E and G to "<" and that bugs the heck out of me.

    I do not think that eTrade or Schawb deal with gold.
     
    Robert Baer, Feb 14, 2014
    #3
  4. "Robert Baer" <> wrote in message
    news:nzgLu.570929$...
    > Jeff Strickland wrote:
    >>
    >> "Robert Baer" <> wrote in message
    >> news:zTTKu.327155$...
    >>> I have an Excel spreadsheet with dates and prices of Gold since Dec 9,
    >>> each older date, price below more recent one (don't ask).
    >>>
    >>> Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    >>> buy10 in F=IF(C3>D3,"buy","sell"),
    >>> amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    >>> Total of J negative; SAME amount if buy/sell IF switched ("<"
    >>> instead of ">").
    >>> Why?
    >>>
    >>>

    >>
    >> Shouldn't you be averaging the price (column B)?

    > * Goofed in write-up here: C=AVERAGE(B3:B12).
    > D=AVERAGE(B3:B22) for 20 days
    >


    Cool, so I spotted a miskey, not problem.




    >>
    >> C = ave(b<start>:b<end>) (B3:B12) using your example of the code you
    >> have.
    >>
    >> How are you populating the cells in column D. You call an IF for the
    >> value of C and D, and I wonder where you filled D because it is not
    >> defined in your formula. You have several cells, columns of cells, that
    >> you are calling in your code, there is no indication that they hold any
    >> values, or where the values come from.

    > *
    >>
    >> It looks like you are either pulling data, or manually entering it, and
    >> you have the date (coilumn A) and the price (column B). It seems to me
    >> that you should be averaging the 10-day price based on the contents of
    >> column B, and putting the result in column C. For example A1 is ten days
    >> ago, B1 is the closing price on that day. A10 is todays date, B10 is
    >> todays price, C10 is the average of B1:B10. Tomorrow, you are averaging
    >> B2:B11 and putting the result in C11. (I have not figured out what cells
    >> are inclusive to ten days, but you should see the pattern here.)
    >>
    >> What you get building up in column C is the running average of the ten
    >> previous days of column B. Then you set the floor and ceiling of the
    >> value of C and either buy more or sell what you have to limit your loss.
    >>
    >> Seems to me that eTrade and Schawb already have tools like this. You
    >> should not have to write this from scratch, although I surely understand
    >> thrill-of-the-battle in doing so.
    >>

    > For more complete info:
    > E=IF(B3>C3,"buy","sell") for buy10
    > F=IF(E3<>E4,B3,F4) for amount10



    E= text, ("buy" or "sell") depending on whether B is more or less than C.

    I'm not arguing with you here, but it appears you are analyzing a text
    string for <>. I wonder if you should be checking it for null ("") instead.

    I'm probably the last guy that should be giving coding advice, but the scant
    level of formula formation that I have under my belt says that you are
    trying to put data into a cell based upon the evaluation of cells that are
    empty when you are looking at them. I think you have your formula built
    wrong, but I can't tell you where.

    Looking at the value of F (this is a column, not a cell, I expect to see F3,
    for example), based on the outcome of an IF statement. Do you have the
    expected result for this IF? You have variables, buy10 and buy20 for
    example, do these variables contain the expected values?



    > G=IF(B3>D3,"buy","sell") for buy20
    > H=IF(G3<>G4,B3,H4) for amount20
    > I=(F3-F4) for gain10
    > J=(H3-H4) for gain20.
    > Current data "on top" or row 3,oldest data (so far) "on bottom" or row
    > 542 (12/9/2011).
    > Easy as all heck to add older data in spare time.
    > K=SUM(I3:I528) for Total10
    > L=SUM(J3:J528) for Total20.
    > K and L are negative, and the numbers DO NOT change if i change the ">"
    > in formula E and G to "<" and that bugs the heck out of me.
    >


    You are summing the contents of cells in both I and J and coming up with
    negative numbers? The only way that can happen is if the numbers in the
    cells being summed are negative.

    I think what I would do here is to make a second spreadsheet for a moment to
    play with the formula using known values. If I can get my formula to giver
    the expected results reliably, then I would have to assume that the contents
    of the cells where the unexpected results are coming from must contain
    values that are outside of my expected range -- if I can make my test work
    right each and every time, then the data on the spreadsheet that produces
    the "wrong" results must somehow be faulty data. Is it possible that the
    faulty data is a mix of numeric and text cells, where the numeric values are
    properly summed and the text -- while numeric characters -- is not summed?

    If you have three cells all containing a 3 character, but one of them is a
    text character while two are numeric, the result of summing is 6. Nevermind,
    that test turns out to give 9 as a result even if all cells are text, but
    ONLY if there are no spaces or alpha characters included in the cell value.

    I just filled three cells with the 3 character, and made some of them
    numeric cells and some text, and all of them text. I used =SUM(A1:A3) to
    fill the value of A5. If A1 was only the digit, 3, then the result of A5 is
    always 9. But if A1 is text and contains an alpha character or a space, then
    the value of A5 is only 6. The contents of a text cell are ignored in the
    =SUM statement if the text cell is not entirely numeric.

    Perhaps your negative numbers are arising out of a problem where the =SUM,
    or =AVERAGE, statement is looking at faulty data that comes from a Format
    Cell problem.


    > I do not think that eTrade or Schawb deal with gold.
    >



    Even if they had the tools, you could not use them unless your accounts were
    there.
     
    Jeff Strickland, Feb 14, 2014
    #4
  5. Robert Baer

    Robert Baer Guest

    Jeff Strickland wrote:
    >
    > "Robert Baer" <> wrote in message
    > news:nzgLu.570929$...
    >> Jeff Strickland wrote:
    >>>
    >>> "Robert Baer" <> wrote in message
    >>> news:zTTKu.327155$...
    >>>> I have an Excel spreadsheet with dates and prices of Gold since Dec 9,
    >>>> each older date, price below more recent one (don't ask).
    >>>>
    >>>> Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    >>>> buy10 in F=IF(C3>D3,"buy","sell"),
    >>>> amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    >>>> Total of J negative; SAME amount if buy/sell IF switched ("<"
    >>>> instead of ">").
    >>>> Why?
    >>>>
    >>>>
    >>>
    >>> Shouldn't you be averaging the price (column B)?

    >> * Goofed in write-up here: C=AVERAGE(B3:B12).
    >> D=AVERAGE(B3:B22) for 20 days
    >>

    >
    > Cool, so I spotted a miskey, not problem.
    >
    >
    >
    >
    >>>
    >>> C = ave(b<start>:b<end>) (B3:B12) using your example of the code you
    >>> have.
    >>>
    >>> How are you populating the cells in column D. You call an IF for the
    >>> value of C and D, and I wonder where you filled D because it is not
    >>> defined in your formula. You have several cells, columns of cells, that
    >>> you are calling in your code, there is no indication that they hold any
    >>> values, or where the values come from.

    >> *
    >>>
    >>> It looks like you are either pulling data, or manually entering it, and
    >>> you have the date (coilumn A) and the price (column B). It seems to me
    >>> that you should be averaging the 10-day price based on the contents of
    >>> column B, and putting the result in column C. For example A1 is ten days
    >>> ago, B1 is the closing price on that day. A10 is todays date, B10 is
    >>> todays price, C10 is the average of B1:B10. Tomorrow, you are averaging
    >>> B2:B11 and putting the result in C11. (I have not figured out what cells
    >>> are inclusive to ten days, but you should see the pattern here.)
    >>>
    >>> What you get building up in column C is the running average of the ten
    >>> previous days of column B. Then you set the floor and ceiling of the
    >>> value of C and either buy more or sell what you have to limit your loss.
    >>>
    >>> Seems to me that eTrade and Schawb already have tools like this. You
    >>> should not have to write this from scratch, although I surely understand
    >>> thrill-of-the-battle in doing so.
    >>>

    >> For more complete info:
    >> E=IF(B3>C3,"buy","sell") for buy10
    >> F=IF(E3<>E4,B3,F4) for amount10

    >
    >
    > E= text, ("buy" or "sell") depending on whether B is more or less than C.
    >
    > I'm not arguing with you here, but it appears you are analyzing a text
    > string for <>. I wonder if you should be checking it for null ("") instead.

    * Excel defines the first arg as a logical test,second arg as value if
    true, 3rd arg as value if false.
    There are no text strings; B and C are currency values and there are
    no nulls.

    >
    > I'm probably the last guy that should be giving coding advice, but the
    > scant level of formula formation that I have under my belt says that you
    > are trying to put data into a cell based upon the evaluation of cells
    > that are empty when you are looking at them. I think you have your
    > formula built wrong, but I can't tell you where.

    * Cols A,B start pre-filled; Cols C,D are calculated numeric values and
    check as being correct; cols E,G results (buy,sell) have been checked as
    correct.

    >
    > Looking at the value of F (this is a column, not a cell, I expect to see
    > F3, for example), based on the outcome of an IF statement. Do you have
    > the expected result for this IF? You have variables, buy10 and buy20 for
    > example, do these variables contain the expected values?

    * All cells in cols E,F are correct to row 529 (rows 530 to end 542, a
    20 day period i think) must be ignored.

    >
    >
    >
    >> G=IF(B3>D3,"buy","sell") for buy20
    >> H=IF(G3<>G4,B3,H4) for amount20
    >> I=(F3-F4) for gain10
    >> J=(H3-H4) for gain20.
    >> Current data "on top" or row 3,oldest data (so far) "on bottom" or row
    >> 542 (12/9/2011).
    >> Easy as all heck to add older data in spare time.
    >> K=SUM(I3:I528) for Total10
    >> L=SUM(J3:J528) for Total20.
    >> K and L are negative, and the numbers DO NOT change if i change the
    >> ">" in formula E and G to "<" and that bugs the heck out of me.
    >>

    >
    > You are summing the contents of cells in both I and J and coming up with
    > negative numbers? The only way that can happen is if the numbers in the
    > cells being summed are negative.

    * Not quite; one could have all-but-one positive number and tat
    exception be a substantially large negative number.
    At this point, i do not care i get a negative sum; what i care about
    is that if i change the IF compare from "greater than" to "less than",
    that sum does NOT change!

    >
    > I think what I would do here is to make a second spreadsheet for a
    > moment to play with the formula using known values. If I can get my
    > formula to giver the expected results reliably, then I would have to
    > assume that the contents of the cells where the unexpected results are
    > coming from must contain values that are outside of my expected range --
    > if I can make my test work right each and every time, then the data on
    > the spreadsheet that produces the "wrong" results must somehow be faulty
    > data. Is it possible that the faulty data is a mix of numeric and text
    > cells, where the numeric values are properly summed and the text --
    > while numeric characters -- is not summed?

    * All values,from Cols A thru H have been verified, and i do not see any
    "wrong" result; the problem is that if i change the IF compare from
    "greater than" to "less than", that sum does NOT change!
    Obviously i am missing something and i thought a different pair of
    eyes would find that.

    >
    > If you have three cells all containing a 3 character, but one of them is
    > a text character while two are numeric, the result of summing is 6.
    > Nevermind, that test turns out to give 9 as a result even if all cells
    > are text, but ONLY if there are no spaces or alpha characters included
    > in the cell value.

    * The only text columns are E and G and the formula(s) give no spaces or
    trailing blanks. Except for col A, all other columns are calculated
    (currency) numeric values and so cannot have or contain text.

    >
    > I just filled three cells with the 3 character, and made some of them
    > numeric cells and some text, and all of them text. I used =SUM(A1:A3) to
    > fill the value of A5. If A1 was only the digit, 3, then the result of A5
    > is always 9. But if A1 is text and contains an alpha character or a
    > space, then the value of A5 is only 6. The contents of a text cell are
    > ignored in the =SUM statement if the text cell is not entirely numeric.
    >
    > Perhaps your negative numbers are arising out of a problem where the
    > =SUM, or =AVERAGE, statement is looking at faulty data that comes from a
    > Format Cell problem.
    >
    >
    >> I do not think that eTrade or Schawb deal with gold.
    >>

    >
    >
    > Even if they had the tools, you could not use them unless your accounts
    > were there.

    EXACTLY!
    I have posted the full spreadsheet at
    http://www.oil4lessllc.org/Gold/ for your perusal.

    Thanks for our persistence.
     
    Robert Baer, Feb 14, 2014
    #5
  6. "Robert Baer" <> wrote in message
    news:rMtLu.344373$...
    > Jeff Strickland wrote:
    >>
    >> "Robert Baer" <> wrote in message
    >> news:nzgLu.570929$...
    >>> Jeff Strickland wrote:
    >>>>
    >>>> "Robert Baer" <> wrote in message
    >>>> news:zTTKu.327155$...
    >>>>> I have an Excel spreadsheet with dates and prices of Gold since Dec 9,
    >>>>> each older date, price below more recent one (don't ask).
    >>>>>
    >>>>> Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    >>>>> buy10 in F=IF(C3>D3,"buy","sell"),
    >>>>> amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    >>>>> Total of J negative; SAME amount if buy/sell IF switched ("<"
    >>>>> instead of ">").
    >>>>> Why?
    >>>>>
    >>>>>
    >>>>
    >>>> Shouldn't you be averaging the price (column B)?
    >>> * Goofed in write-up here: C=AVERAGE(B3:B12).
    >>> D=AVERAGE(B3:B22) for 20 days
    >>>

    >>
    >> Cool, so I spotted a miskey, not problem.
    >>
    >>
    >>
    >>
    >>>>
    >>>> C = ave(b<start>:b<end>) (B3:B12) using your example of the code you
    >>>> have.
    >>>>
    >>>> How are you populating the cells in column D. You call an IF for the
    >>>> value of C and D, and I wonder where you filled D because it is not
    >>>> defined in your formula. You have several cells, columns of cells, that
    >>>> you are calling in your code, there is no indication that they hold any
    >>>> values, or where the values come from.
    >>> *
    >>>>
    >>>> It looks like you are either pulling data, or manually entering it, and
    >>>> you have the date (coilumn A) and the price (column B). It seems to me
    >>>> that you should be averaging the 10-day price based on the contents of
    >>>> column B, and putting the result in column C. For example A1 is ten
    >>>> days
    >>>> ago, B1 is the closing price on that day. A10 is todays date, B10 is
    >>>> todays price, C10 is the average of B1:B10. Tomorrow, you are averaging
    >>>> B2:B11 and putting the result in C11. (I have not figured out what
    >>>> cells
    >>>> are inclusive to ten days, but you should see the pattern here.)
    >>>>
    >>>> What you get building up in column C is the running average of the ten
    >>>> previous days of column B. Then you set the floor and ceiling of the
    >>>> value of C and either buy more or sell what you have to limit your
    >>>> loss.
    >>>>
    >>>> Seems to me that eTrade and Schawb already have tools like this. You
    >>>> should not have to write this from scratch, although I surely
    >>>> understand
    >>>> thrill-of-the-battle in doing so.
    >>>>
    >>> For more complete info:
    >>> E=IF(B3>C3,"buy","sell") for buy10
    >>> F=IF(E3<>E4,B3,F4) for amount10

    >>
    >>
    >> E= text, ("buy" or "sell") depending on whether B is more or less than C.
    >>
    >> I'm not arguing with you here, but it appears you are analyzing a text
    >> string for <>. I wonder if you should be checking it for null ("")
    >> instead.

    > * Excel defines the first arg as a logical test,second arg as value if
    > true, 3rd arg as value if false.
    > There are no text strings; B and C are currency values and there are no
    > nulls.
    >
    >>
    >> I'm probably the last guy that should be giving coding advice, but the
    >> scant level of formula formation that I have under my belt says that you
    >> are trying to put data into a cell based upon the evaluation of cells
    >> that are empty when you are looking at them. I think you have your
    >> formula built wrong, but I can't tell you where.

    > * Cols A,B start pre-filled; Cols C,D are calculated numeric values and
    > check as being correct; cols E,G results (buy,sell) have been checked as
    > correct.
    >
    >>
    >> Looking at the value of F (this is a column, not a cell, I expect to see
    >> F3, for example), based on the outcome of an IF statement. Do you have
    >> the expected result for this IF? You have variables, buy10 and buy20 for
    >> example, do these variables contain the expected values?

    > * All cells in cols E,F are correct to row 529 (rows 530 to end 542, a 20
    > day period i think) must be ignored.
    >
    >>
    >>
    >>
    >>> G=IF(B3>D3,"buy","sell") for buy20
    >>> H=IF(G3<>G4,B3,H4) for amount20
    >>> I=(F3-F4) for gain10
    >>> J=(H3-H4) for gain20.
    >>> Current data "on top" or row 3,oldest data (so far) "on bottom" or row
    >>> 542 (12/9/2011).
    >>> Easy as all heck to add older data in spare time.
    >>> K=SUM(I3:I528) for Total10
    >>> L=SUM(J3:J528) for Total20.
    >>> K and L are negative, and the numbers DO NOT change if i change the
    >>> ">" in formula E and G to "<" and that bugs the heck out of me.
    >>>

    >>
    >> You are summing the contents of cells in both I and J and coming up with
    >> negative numbers? The only way that can happen is if the numbers in the
    >> cells being summed are negative.

    > * Not quite; one could have all-but-one positive number and tat exception
    > be a substantially large negative number.
    > At this point, i do not care i get a negative sum; what i care about is
    > that if i change the IF compare from "greater than" to "less than", that
    > sum does NOT change!
    >
    >>
    >> I think what I would do here is to make a second spreadsheet for a
    >> moment to play with the formula using known values. If I can get my
    >> formula to giver the expected results reliably, then I would have to
    >> assume that the contents of the cells where the unexpected results are
    >> coming from must contain values that are outside of my expected range --
    >> if I can make my test work right each and every time, then the data on
    >> the spreadsheet that produces the "wrong" results must somehow be faulty
    >> data. Is it possible that the faulty data is a mix of numeric and text
    >> cells, where the numeric values are properly summed and the text --
    >> while numeric characters -- is not summed?

    > * All values,from Cols A thru H have been verified, and i do not see any
    > "wrong" result; the problem is that if i change the IF compare from
    > "greater than" to "less than", that sum does NOT change!
    > Obviously i am missing something and i thought a different pair of eyes
    > would find that.
    >



    Okay, I'm getting it -- the trouble isn't the result so much as the result
    does not change even if you alter the formula. Does the value of the number
    change, go from a negative<whatever> to a different negative? If the result
    of your comparison is the same, then the =SUM is working, and whether the
    contents is < or > would not change, would it?

    Summing for I3 to I500 is the same. You define the cells of I as a
    calculation of two cells in F, then later you sum multiple cells of I to
    arrive at a result. The result of the sum in the code you presented is not
    dependent on < or >, so changing the < to a > should result in the same sum
    of the cells in I.

    To be fair to you, you are writing code that exceeds any that I have
    written. The best code I have done so far is to calculate the diameter of a
    tire and then display the various characteristics, tread width, sidewall
    height, revolutions per mile, and so on, of the different tire
    specifications I plug in. I can tell you that if you have a 225/45x17 on
    your car, you will have an almost identical tire if you went to a 225/40x18,
    and tell you that the difference in tire rotation is that one goes 807
    revolutions per mile and the other goes 803, meaning there is no functional
    difference between the tires you own and what you might want to install.

    The best I can do is tickle your brain and have you slap yourself silly for
    not seeing the obvious, and if I can find the fault, then it's obvious.




    >>
    >> If you have three cells all containing a 3 character, but one of them is
    >> a text character while two are numeric, the result of summing is 6.
    >> Nevermind, that test turns out to give 9 as a result even if all cells
    >> are text, but ONLY if there are no spaces or alpha characters included
    >> in the cell value.

    > * The only text columns are E and G and the formula(s) give no spaces or
    > trailing blanks. Except for col A, all other columns are calculated
    > (currency) numeric values and so cannot have or contain text.
    >


    Even still, if the =SUM statement gives the right answer, then the format of
    the data as text or numeric is not a problem. I was only brainstorming, and
    that turned out to be a dud.
     
    Jeff Strickland, Feb 14, 2014
    #6
  7. Robert Baer

    Robert Baer Guest

    Jeff Strickland wrote:
    >
    > "Robert Baer" <> wrote in message
    > news:rMtLu.344373$...
    >> Jeff Strickland wrote:
    >>>
    >>> "Robert Baer" <> wrote in message
    >>> news:nzgLu.570929$...
    >>>> Jeff Strickland wrote:
    >>>>>
    >>>>> "Robert Baer" <> wrote in message
    >>>>> news:zTTKu.327155$...
    >>>>>> I have an Excel spreadsheet with dates and prices of Gold since
    >>>>>> Dec 9,
    >>>>>> each older date, price below more recent one (don't ask).
    >>>>>>
    >>>>>> Columns: A=date, B=price, avg10 in C=AVERAGE(C3:C12),
    >>>>>> buy10 in F=IF(C3>D3,"buy","sell"),
    >>>>>> amt10 in G=IF(F3<>F4,C3,G4), gain10 in J=(G3-G4).
    >>>>>> Total of J negative; SAME amount if buy/sell IF switched ("<"
    >>>>>> instead of ">").
    >>>>>> Why?
    >>>>>>
    >>>>>>
    >>>>>
    >>>>> Shouldn't you be averaging the price (column B)?
    >>>> * Goofed in write-up here: C=AVERAGE(B3:B12).
    >>>> D=AVERAGE(B3:B22) for 20 days
    >>>>
    >>>
    >>> Cool, so I spotted a miskey, not problem.
    >>>
    >>>
    >>>
    >>>
    >>>>>
    >>>>> C = ave(b<start>:b<end>) (B3:B12) using your example of the code you
    >>>>> have.
    >>>>>
    >>>>> How are you populating the cells in column D. You call an IF for the
    >>>>> value of C and D, and I wonder where you filled D because it is not
    >>>>> defined in your formula. You have several cells, columns of cells,
    >>>>> that
    >>>>> you are calling in your code, there is no indication that they hold
    >>>>> any
    >>>>> values, or where the values come from.
    >>>> *
    >>>>>
    >>>>> It looks like you are either pulling data, or manually entering it,
    >>>>> and
    >>>>> you have the date (coilumn A) and the price (column B). It seems to me
    >>>>> that you should be averaging the 10-day price based on the contents of
    >>>>> column B, and putting the result in column C. For example A1 is ten
    >>>>> days
    >>>>> ago, B1 is the closing price on that day. A10 is todays date, B10 is
    >>>>> todays price, C10 is the average of B1:B10. Tomorrow, you are
    >>>>> averaging
    >>>>> B2:B11 and putting the result in C11. (I have not figured out what
    >>>>> cells
    >>>>> are inclusive to ten days, but you should see the pattern here.)
    >>>>>
    >>>>> What you get building up in column C is the running average of the ten
    >>>>> previous days of column B. Then you set the floor and ceiling of the
    >>>>> value of C and either buy more or sell what you have to limit your
    >>>>> loss.
    >>>>>
    >>>>> Seems to me that eTrade and Schawb already have tools like this. You
    >>>>> should not have to write this from scratch, although I surely
    >>>>> understand
    >>>>> thrill-of-the-battle in doing so.
    >>>>>
    >>>> For more complete info:
    >>>> E=IF(B3>C3,"buy","sell") for buy10
    >>>> F=IF(E3<>E4,B3,F4) for amount10
    >>>
    >>>
    >>> E= text, ("buy" or "sell") depending on whether B is more or less
    >>> than C.
    >>>
    >>> I'm not arguing with you here, but it appears you are analyzing a text
    >>> string for <>. I wonder if you should be checking it for null ("")
    >>> instead.

    >> * Excel defines the first arg as a logical test,second arg as value if
    >> true, 3rd arg as value if false.
    >> There are no text strings; B and C are currency values and there are
    >> no nulls.
    >>
    >>>
    >>> I'm probably the last guy that should be giving coding advice, but the
    >>> scant level of formula formation that I have under my belt says that you
    >>> are trying to put data into a cell based upon the evaluation of cells
    >>> that are empty when you are looking at them. I think you have your
    >>> formula built wrong, but I can't tell you where.

    >> * Cols A,B start pre-filled; Cols C,D are calculated numeric values
    >> and check as being correct; cols E,G results (buy,sell) have been
    >> checked as correct.
    >>
    >>>
    >>> Looking at the value of F (this is a column, not a cell, I expect to see
    >>> F3, for example), based on the outcome of an IF statement. Do you have
    >>> the expected result for this IF? You have variables, buy10 and buy20 for
    >>> example, do these variables contain the expected values?

    >> * All cells in cols E,F are correct to row 529 (rows 530 to end 542, a
    >> 20 day period i think) must be ignored.
    >>
    >>>
    >>>
    >>>
    >>>> G=IF(B3>D3,"buy","sell") for buy20
    >>>> H=IF(G3<>G4,B3,H4) for amount20
    >>>> I=(F3-F4) for gain10
    >>>> J=(H3-H4) for gain20.
    >>>> Current data "on top" or row 3,oldest data (so far) "on bottom" or row
    >>>> 542 (12/9/2011).
    >>>> Easy as all heck to add older data in spare time.
    >>>> K=SUM(I3:I528) for Total10
    >>>> L=SUM(J3:J528) for Total20.
    >>>> K and L are negative, and the numbers DO NOT change if i change the
    >>>> ">" in formula E and G to "<" and that bugs the heck out of me.
    >>>>
    >>>
    >>> You are summing the contents of cells in both I and J and coming up with
    >>> negative numbers? The only way that can happen is if the numbers in the
    >>> cells being summed are negative.

    >> * Not quite; one could have all-but-one positive number and tat
    >> exception be a substantially large negative number.
    >> At this point, i do not care i get a negative sum; what i care about
    >> is that if i change the IF compare from "greater than" to "less than",
    >> that sum does NOT change!
    >>
    >>>
    >>> I think what I would do here is to make a second spreadsheet for a
    >>> moment to play with the formula using known values. If I can get my
    >>> formula to giver the expected results reliably, then I would have to
    >>> assume that the contents of the cells where the unexpected results are
    >>> coming from must contain values that are outside of my expected range --
    >>> if I can make my test work right each and every time, then the data on
    >>> the spreadsheet that produces the "wrong" results must somehow be faulty
    >>> data. Is it possible that the faulty data is a mix of numeric and text
    >>> cells, where the numeric values are properly summed and the text --
    >>> while numeric characters -- is not summed?

    >> * All values,from Cols A thru H have been verified, and i do not see
    >> any "wrong" result; the problem is that if i change the IF compare
    >> from "greater than" to "less than", that sum does NOT change!
    >> Obviously i am missing something and i thought a different pair of
    >> eyes would find that.
    >>

    >
    >
    > Okay, I'm getting it -- the trouble isn't the result so much as the
    > result does not change even if you alter the formula. Does the value of
    > the number change, go from a negative<whatever> to a different negative?
    > If the result of your comparison is the same, then the =SUM is working,
    > and whether the contents is < or > would not change, would it?

    * From your comment, i looked more carefully at my spreadsheet.
    Note comment at top, indicating calcs from E thru L are for "greater
    than", and calcs from M thru T are for "less than".
    And by golly, E and G really are for "greater than", and calcs from
    M and O are for "less than".
    So far, so good.
    I note that cols F and N are identical as well as cols H and P are
    identical.
    Seems reasonable for values to use for "pick".
    AHA!!!!!!!!!!!!!!
    Look at formula in col I vs col Q; they are the SAME!
    Found sub, sank same.
    That makes the "Sell>avg, buy<avg" (second set of calcs) positive
    with absolute value same.
    Precisely correct.
    And seeing the 20 day larger also makes sense.

    >
    > Summing for I3 to I500 is the same. You define the cells of I as a
    > calculation of two cells in F, then later you sum multiple cells of I to
    > arrive at a result. The result of the sum in the code you presented is
    > not dependent on < or >, so changing the < to a > should result in the
    > same sum of the cells in I.
    >
    > To be fair to you, you are writing code that exceeds any that I have
    > written. The best code I have done so far is to calculate the diameter
    > of a tire and then display the various characteristics, tread width,
    > sidewall height, revolutions per mile, and so on, of the different tire
    > specifications I plug in. I can tell you that if you have a 225/45x17 on
    > your car, you will have an almost identical tire if you went to a
    > 225/40x18, and tell you that the difference in tire rotation is that one
    > goes 807 revolutions per mile and the other goes 803, meaning there is
    > no functional difference between the tires you own and what you might
    > want to install.

    * I would not even hazard to attempt that; way out of my league.

    >
    > The best I can do is tickle your brain and have you slap yourself silly
    > for not seeing the obvious, and if I can find the fault, then it's obvious.
    >
    >
    >
    >
    >>>
    >>> If you have three cells all containing a 3 character, but one of them is
    >>> a text character while two are numeric, the result of summing is 6.
    >>> Nevermind, that test turns out to give 9 as a result even if all cells
    >>> are text, but ONLY if there are no spaces or alpha characters included
    >>> in the cell value.

    >> * The only text columns are E and G and the formula(s) give no spaces
    >> or trailing blanks. Except for col A, all other columns are calculated
    >> (currency) numeric values and so cannot have or contain text.
    >>

    >
    > Even still, if the =SUM statement gives the right answer, then the
    > format of the data as text or numeric is not a problem. I was only
    > brainstorming, and that turned out to be a dud.
    >
    >
    >
    >

    Thanks for the nudge!
     
    Robert Baer, Feb 15, 2014
    #7
  8. "Robert Baer" <> wrote in message
    news:NpDLu.563821$...

    > * I would not even hazard to attempt that; way out of my league.
    >


    It's actually pretty easy. In the size, 225/45x17, 225 is the width in mm,
    45 is a multiplier (45%) of 225 that yields the sidewall height, and 17 is
    the diameter of the wheel that the tire fits on.

    225 x .45 = 101.25, or so. Divide mm by 25.4 to convert to inches, then
    multiply by 2 because there are two sidewalls in the overall diameter, then
    add in the wheel size, 17 in this example, and the total diameter becomes
    just under 25 inches. Muptiply by pi to get the circumference and then
    convert this number intoi feet and divide it into a mile, and the rusult is
    the numer of revolutions per mile for the tire. Knowing this information for
    one tire, you can then compare it against a second tire to see if what you
    want to change to will result in modifications being required to things like
    the gear ratio or if there will be a change to thge accuracy of the
    speedometer. Easy stuff, really.



    >>
    >> The best I can do is tickle your brain and have you slap yourself silly
    >> for not seeing the obvious, and if I can find the fault, then it's
    >> obvious.
    >>
    >>
    >>
    >>
    >>>>
    >>>> If you have three cells all containing a 3 character, but one of them
    >>>> is
    >>>> a text character while two are numeric, the result of summing is 6.
    >>>> Nevermind, that test turns out to give 9 as a result even if all cells
    >>>> are text, but ONLY if there are no spaces or alpha characters included
    >>>> in the cell value.
    >>> * The only text columns are E and G and the formula(s) give no spaces
    >>> or trailing blanks. Except for col A, all other columns are calculated
    >>> (currency) numeric values and so cannot have or contain text.
    >>>

    >>
    >> Even still, if the =SUM statement gives the right answer, then the
    >> format of the data as text or numeric is not a problem. I was only
    >> brainstorming, and that turned out to be a dud.
    >>
    >>
    >>
    >>

    > Thanks for the nudge!
    >


    No worries. You no longer have a result that is the same whether the test is
    < or >, right? (You fixed it?)
     
    Jeff Strickland, Feb 15, 2014
    #8
  9. Robert Baer

    Robert Baer Guest

    Jeff Strickland wrote:
    >
    > "Robert Baer" <> wrote in message
    > news:NpDLu.563821$...
    >
    >> * I would not even hazard to attempt that; way out of my league.
    >>

    >
    > It's actually pretty easy. In the size, 225/45x17, 225 is the width in
    > mm, 45 is a multiplier (45%) of 225 that yields the sidewall height, and
    > 17 is the diameter of the wheel that the tire fits on.
    >
    > 225 x .45 = 101.25, or so. Divide mm by 25.4 to convert to inches, then
    > multiply by 2 because there are two sidewalls in the overall diameter,
    > then add in the wheel size, 17 in this example, and the total diameter
    > becomes just under 25 inches. Muptiply by pi to get the circumference
    > and then convert this number intoi feet and divide it into a mile, and
    > the rusult is the numer of revolutions per mile for the tire. Knowing
    > this information for one tire, you can then compare it against a second
    > tire to see if what you want to change to will result in modifications
    > being required to things like the gear ratio or if there will be a
    > change to thge accuracy of the speedometer. Easy stuff, really.
    >
    >
    >
    >>>
    >>> The best I can do is tickle your brain and have you slap yourself silly
    >>> for not seeing the obvious, and if I can find the fault, then it's
    >>> obvious.
    >>>
    >>>
    >>>
    >>>
    >>>>>
    >>>>> If you have three cells all containing a 3 character, but one of
    >>>>> them is
    >>>>> a text character while two are numeric, the result of summing is 6.
    >>>>> Nevermind, that test turns out to give 9 as a result even if all cells
    >>>>> are text, but ONLY if there are no spaces or alpha characters included
    >>>>> in the cell value.
    >>>> * The only text columns are E and G and the formula(s) give no spaces
    >>>> or trailing blanks. Except for col A, all other columns are calculated
    >>>> (currency) numeric values and so cannot have or contain text.
    >>>>
    >>>
    >>> Even still, if the =SUM statement gives the right answer, then the
    >>> format of the data as text or numeric is not a problem. I was only
    >>> brainstorming, and that turned out to be a dud.
    >>>
    >>>
    >>>
    >>>

    >> Thanks for the nudge!
    >>

    >
    > No worries. You no longer have a result that is the same whether the
    > test is < or >, right? (You fixed it?)
    >
    >

    Correct; one way the numbers are negative, and the other way the
    numbers are positive; like it should be.
    Shows standard rule to buy low,sell high.
     
    Robert Baer, Feb 16, 2014
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. enjay

    Excel Problem

    enjay, Jul 22, 2003, in forum: Computer Support
    Replies:
    2
    Views:
    427
    Alexander Rogge
    Jul 23, 2003
  2. Jan

    Excel problem

    Jan, Nov 24, 2003, in forum: Computer Support
    Replies:
    3
    Views:
    465
    Blinky the Shark
    Nov 24, 2003
  3. Andy Archard

    Excel mouse problem

    Andy Archard, Jan 22, 2004, in forum: Computer Support
    Replies:
    0
    Views:
    3,128
    Andy Archard
    Jan 22, 2004
  4. Gunjani

    Excel 2002 Macro problem

    Gunjani, May 7, 2004, in forum: Computer Support
    Replies:
    5
    Views:
    4,367
  5. Kuisse0002

    excel software problem?

    Kuisse0002, Jul 3, 2004, in forum: Computer Support
    Replies:
    5
    Views:
    1,682
    Toolman Tim
    Jul 4, 2004
Loading...

Share This Page