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 ">").
    Robert Baer, Feb 12, 2014
    1. Advertisements

  2. 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
    1. Advertisements

  3. Robert Baer

    Robert Baer Guest

    * Goofed in write-up here: C=AVERAGE(B3:B12).
    D=AVERAGE(B3:B22) for 20 days
    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
  4. Cool, so I spotted a miskey, not problem.

    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?

    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.

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

    Robert Baer Guest

    * 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.
    * 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
    * 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.
    * 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!
    * 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.
    * 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 have posted the full spreadsheet at
    http://www.oil4lessllc.org/Gold/ for your perusal.

    Thanks for our persistence.
    Robert Baer, Feb 14, 2014

  6. 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.

    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
  7. Robert Baer

    Robert Baer Guest

    * 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
    Seems reasonable for values to use for "pick".
    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.
    * I would not even hazard to attempt that; way out of my league.
    Thanks for the nudge!
    Robert Baer, Feb 15, 2014
  8. 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.

    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
  9. Robert Baer

    Robert Baer Guest

    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
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.