Forum Discussion
Is there a solution to using the same function multiple times?
Workbook attached. Old formula in column D, new formula in column E
Sorry but that logic still doesn't make sense to me but I really don't even know what that logic is trying to find. Maybe if you explain what you are trying to find/calculate we could help with that and not just how to make your formula more concise. Just as a shot in the dark I would expect something more like:
1,5 \ 1,4 \ 1,3 \ 1,2 \ 1,1 \ 1 and then -1\ -1,1 \ -1,2 \ -1,3 \ -1,4 \ -1,5
EDIT: so looking at the numbers I see positive numbers going >100% while negative numbers do not so I'm guessing this a measure of change and the actual value can't go (or extremely unlikely to go) negative to a negative % change would never exceed -100% (i.e. goes to 0). I still don't know what values/cases you are trying to 'pull out' but be < case might then be 0\ -0,1\ -0,2\ ...
I should compare the price change in the relevant period with each growth percentage and if it increased by more than the growth percentage I should get -1 (for each rate) point, if it increased less than the growth percentage I should get +1 (for each rate) point. This is the logic.
In its simplest form, it checks > and < for each growth percentage.
(TOPLA.ÇARPIM((B9<K9:AL9)*(($K$8:$AL$8="QoQ")+($K$8:$AL$8="QoQ TTM"))*(K9:AL9<>""))-TOPLA.ÇARPIM((B9>K9:AL9)*(($K$8:$AL$8="QoQ")+($K$8:$AL$8="QoQ TTM"))*(K9:AL9<>""))+
TOPLA.ÇARPIM((C9<K9:AL9)*(($K$8:$AL$8="YoY")+($K$8:$AL$8="YoY TTM"))*(K9:AL9<>""))-TOPLA.ÇARPIM((C9>K9:AL9)*(($K$8:$AL$8="YoY")+($K$8:$AL$8="YoY TTM"))*(K9:AL9<>""))
I then want to do an additional +/- point check to check how much the price is above/below the growth percentage.
And as you said, here is the real problem. Things get complicated when negative numbers are involved.
mtarler
Restructuring is what I want to do. I hope I can explain what I want to do this time. with 3 different waysI made the calculations only with the "QoQ and QoQ TTM" headings.
If everything is correct, I will need to add the "YoY and YoY TTM" headers to the calculation.- this 'analysis' is still a mystery to me. I think you are trying to give 'points' for being above or below threshold and 1pt for just above/below and 2pts for a bit more and so on for a max of 5pts being really far extreme and then taking the difference between the above and below. but please correct or clarify if you could.
that all said the whole -.9 or -.1 products are still concerning as I don't know they are doing what you think. remember multiplying by -1 on each side of an inequality reverses the inequality to consider if -B9>K9:AL9*{-1, +0.1, +0.2 ....} makes sense to what you want.