Excel problem

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.

 

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

 

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

 

* 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
correct.

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

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

Thanks for our persistence.

 

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.

 

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

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

Thanks for the nudge!

 

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?)

 

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.