% of price drops

[loudnoiseman]

Hi Everyone,

I'm looking for a way to find the % of times the price drops relative to its previous figure within a range. I thought this might be a SUMPRODUCT method but I'm open to suggestions.

Thanks!

 

[Jonmo1]

You can use sumproduct....You just have to offset the range...

Try


=SUMPRODUCT(--(B2:B100 < B1:B99))<?XML:NAMESPACE PREFIX = B1 /><B1:B99))< p>
This gives the count of how many times the price dropped.

99.99 percent sure on my math, but percentage is count/total
So that was 100 cells (B1 to B100) so

=SUMPRODUCT(--(B2:B100 < B1:B99))/100<B1:B99))< p>

</B1:B99))<><B1:B99)) p 100<>
Hope that helps.
</B1:B99))></B1:B99))<>

 

[shg]

The denominator would be 99, would it not?

 

[loudnoiseman]

Perfect!!!
I adapted your formula to: SUMPRODUCT(--(B3:B400 < B2:B399))/COUNT(B:B)
thanks jonmo1

 

[shg]

If there were two prices, how many times could it change?

The denominator should be one less than the number of prices that appear.

 

[Jonmo1]

shg is absolutely correct..
Fallling back on my 99.99% sure of the math part...

So try

SUMPRODUCT(--(B3:B400 < B2:B399))/(COUNT(B:B)-1)

This is assuming there are no numbers in B1 B2 or beyond B400
probably more accurate to use

SUMPRODUCT(--(B3:B400 < B2:B399))/(COUNT(B2:B400)-1)