dividing zero and negative values to unique positive value in set of data

[miss2006]

Hi there,

I have a big list of codes where for each code there is a set of negative and zero values but only one positive value (that is usually the last value in each code set).
I want to get variation between each negative or zero value in the set to its positive value for the same code.

example:

Code Value What i need (variation between each (-)ve value or zero value to its (+)value for same code)

23838 (63) = ((60-(-63))/60)%
23838 60

2413 - = ((55-(0))/55)%
2413 55

2415 (310) = ((81-(-310))/81)%
2415 (141) = ((81-(-141))/81)%
2415 (91) = ((81-(-91))/81)%
2415 - = ((81-(0))/81)%
2415 - = ((81-(0))/81)%
2415 81

please help i was trying for last 3 days without success.

file is attached.

[Richard Buttrey]

Hi and welcome to the forum

One way in D4 as an array formula (enter with Ctrl Shift Enter) copied down

Please Login or Register  to view this content.

[TMS]

Another way...

Please Login or Register  to view this content.

[TMS]

Let's make that

Please Login or Register  to view this content.

to ignore the positive values

[miss2006]

Thanks Richard,

I tried using the formula it worked, but i am not the same results as i needed.?

[miss2006]

THANKSSS A LOOOT TMS ,

IT WOOOORKED.

you made my day

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Richard Buttrey]

Thanks Richard,

I tried using the formula it worked, but i am not the same results as i needed.?

Purely out of interest what were the results you needed and how did they differ to the ones the formula gave?

[miss2006]

Richard Please check the attached to check how the results were different from what i needed

[Richard Buttrey]

Richard Please check the attached to check how the results were different from what i needed

I beg to differ. The formula gives exactly the same results.

What you hadn't done, as I had indicated , was treat the formula as an array formula and enter it by holding down the Citrl-Shift and Enter keys together. When you do you'll see you get the { } parentheses that indicate it's an array formula.

[TMS]

Hi Richard: please try my formula and compare the results. I think they diverge where there are "missing" entries. Can't post the example file right now as I’m out for a few hours but I will post it later.

[Richard Buttrey]

Hi Trevor,

Just checked again and your #4 formula does indeed give the required results, as indeed does my #2 formula if cotrrectly entered as an array formula with CSE. Miss2006 had obviously missed that vital bit.

The only difference in the formulae is that mine requires CSE to enter, yours of course doesn't.

The 'missing' entries (I presume blank rows) are of course catered for with the first IF(B4>0....bit

Cheers.

Richard

[TMS]

Hi Richard, the other thing the OP may not have done is to extend the range to include all 61682 rows.

I was going to upload the updated sample file but, even zipped, it is too large. Maybe need to look at it again and see if I can reduce the saize of it.

We appear to differ on about 1% of the row calculations; 624 to be exact, although the difference may, in some cases, be minimal and not show up with zero decimal places. In these examples, it is typically around 0.04% different.

Anomalies occur whan a) there is no positive number; b) there is more than one positive number and the maximum value is NOT the last value; c) minor issue if there are only positive numbers.

Given that the OP stated:

there is a set of negative and zero values but only one positive value (that is usually the last value in each code set).

that would imply user error. That said, usually is a get out of jail card ... if the last value is required, my formula is probably right; if the maximum value is required, then yours is probably right. However, the data does not comply with the OP's expectations.

I haven't checked all the anomalies as it takes an age to calculate and filter the data so there could be other issues. I have used COUNTIFS to confirm the issue of more than one postive number. It appears there can be as many as 5 positive numbers for a code. Specifially, code 28803479 has 5 positive numbers out of seven values. We agree on the calculation as the largest is also the last value for the code

[Richard Buttrey]

Hi Trevor,

Must admit I hadn't done as much analysis as you have in identifying small differences.

I certainly hadn't spotted that there were more than one +ve number in some places, so well done for sticking at it.
You're probably right that that might confuse Excel. I also wonder if my use of "" instead of zero might have an effect in places.

[TMS]

Hi Richard, well, we seem to agree on maybe 90-95% and, out of 61,682 rows, it's not easy to pick up on data errors.

Just one of those problems that piqued my interest and it has been bugging me. Biggest problem is it's hellish slow to calculate. Both my formula and yours take an age to calculate on their own so adding analysis columns and filters gets a little tedious. It's a case of change something and go and do something else for a bit.

[TMS]

OK, I've reduced the size a bit by converting formulae to values but I still can't reduce the size, even zipped, to less than 2.3Mb

Anyway, if you want to experiment these are the formula:

Mine, refined again, in cell E4 and down:

Please Login or Register  to view this content.

Yours, in cell F4 down:

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter

Simple comparison in G4 down:

Please Login or Register  to view this content.

And counter in H4 down:

Please Login or Register  to view this content.

I selected all the data from A3 down and switched on Autofilter. Filtering on column H identifies the multiple positives, 2, 3 and 5

Seems there's no interest from the OP so s/he may not be aware of the issues with the data and, consequently, the results

[miss2006]

Hi TMS & Richard,
I was out of town for business, now I read your posts.
I need time to understand what you were both discussing,
you are both excel genius.

I wish to be like you one day.
Thank you for highlighting that there are codes with multiple positive values I forget to mention that,
Actually, I have first to rearrange the last date and current date from smallest to highest for each code,
then I can apply the formula to divide all numbers for one code by the positive last number for that code.
Can you please help me to do that? I tried sorting dates from smallest to largest with no success.

Also I want to highlight the full set of codes wherever one value in the set has a variation that is less than (-5,000%),
as I need to analysis the reason for such big variation and to compare the variation of other negative values for that set as well.

Please see the attached excel. ( I have deleted data form raw 68 to 44,932 to reduce size of the file)
but the data ends at cell number 61,682