Comparing current price to previous price and sum total - spreadsheet example

UPDATE!

Disregard what I have previously written if you read it. That was incorrect.

I have made a video to make it clearer: http://screencast.com/t/Xva68k3BaaN

see attached file

I found the explanations somewhat confusing because I couldn't exactly picture how all the requirements would be met. This may not solve your problem but may give an idea, if on the right track, of how to solve it.

I used two helper columns. The first one, column I determined if f4>f3 and f4<f5 if both were TRUE, subtracted F3 from F4.

The second helper column, column I determined if F4 < F3 regardless of other conditions as I could not determine just how that was to be applied to the values in J3 to N3. Where this was TRUE a 1 was entered in column I. The helper columns are in yellow. The calculations for each of the values in J3 to N3 are in J4 to N4.

What solution are you referring to?

What exactly are the calculations that you are after? I found both presentations to be somewhat hard to understand what you want.

Originally Posted by newdoverman

What solution are you referring to?

What exactly are the calculations that you are after? I found both presentations to be somewhat hard to understand what you want.

Just glanced at what you've done and my response was to nflsales.

Without comparing the two spreadsheets you've kindly done - and glancing briefly at what you've done newdoverman - yours looks pretty much spot on for what I am after.

Using what you have kindly done to help me, I have made tiny changes to help explain further what I am actually wanting to gain from this spread-sheet. As this spread-sheet currently stands in this version, everything is what I am after minus one more thing (pretty much).

Please see attached.

I think that I have interpreted what you want. You should now be able to calculate the percentages that you want.

You say that you want to "flip". I'm not sure exactly how you want this done.

This is the main statement that get repeated throughout the formula: IF(AND($F4>$F3,$F4-$F3>=$P$2,$F5>$F4),"Failed Pips 0.0011"

What you refer to as "Brown" is F5

What you refer to as "Green" is F3

What you refer to as "Blue" is F4

I don't really understand what you need to be "flipped" and would appreciate a clarification.

This will require an additional helper column as it cannot be included in column J. This file has made provision for that additional column and changes have been made to the formula in column J so that it can be used, with the modifications that you want.

The enclosed file isn't the last part solution but the groundwork.

The part of the formula that you quote is correct for the part of the formula that it is found in. The difference F4-f3 is being compared to the minimum value for Pips 0.0003

You will notice if you follow the formula from beginning to end that it is progressively comparing that difference to the minimum value for the Pips. All the minimum Pip values in descending order. This determines if the value calculated is between one Pip and another.

If you were to add the less than 0.0005 to the conditions, you would be eliminating all values pertaining to the Pips with values greater than 0.0005. The IF(AND(.....) means that all the elements between the () must be TRUE for the statement to be correct. One element that is not TRUE will render the whole part FALSE. In this case, the calculations for all Pips with values greater than 0.0005 would be FALSE and nothing would be returned for them.

That means that if you want to make a different statistical calculation, it will have to be in a column other than J because the added condition will be contradictory to what is already there. If all is correct up to column J and the values returned satisfy what you are looking for to that point then you have to decide exactly what it is that you are wanting to calculate that is not already included in the calculations. This would then be entered into column K and the summary of that can then be calculated under each Pip.

If H18 does equal 0.0003 then the subtraction will take place. If H18 does not equal 0.0003 the result will be zero.

=IF(H18=0 is the Logical Test. If the test is TRUE, then the TRUE part of the formula will be returned that is E17-E18. If the Logical Test is FALSE, 0 will be returned. If E17 or E18 or both have a non numeric value, a #VALUE! error will be returned.

I think that H20 must be formatted as text before the number 0.0003 is entered. That is the only way that I can reproduce your problem.

Delete the contents of H20. Format H20 as GENERAL and enter 0.0003 back into the cell. If H20 is being filled via formula multiply the formula by 1 or add 0, to change the formula result to a number.

You are misunderstanding. The formula that you have in H20 is delivering what appears to be a number but it is really TEXT that looks like a number. All that you have to do is to append to the end of the formula a +0 or multiply by 1 to convert the TEXT to a NUMBER. Once converted to a number, both formulae will work just fine.

If you are still having trouble after doing this, upload your worksheet and I will take a look at it for you.