Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
I would like to build a formula that allows me to
go back 50 records in the database to locate and list how many records since the last time the farthest back of the 3 evaluated cells last occurred.
example:
,,,,,,,,,,,,C42
9,,,,6,,,,2
,,,,,,,,,,,,C48
0,,,,7,,,3
A49
7,,,,4,,,,1
A51 B51 C51 (The evaluated cells)
3,,,,7,,,,2
Therefore, the result would list 9, because from the cell values
3,7,2, #2 was the furtherest back in the last 50 records.
It last occurred 9 records ago.
I can't understand your requirements. Can you post an example?
See attachment
I don't know if the proper protocol is to reply or edit my post so I choose to reply:
I would like to build a formula that allows me to
go back 50 records in the database to locate and list how many records since the last time the farthest back of the 3 evaluated cells last occurred.
F28 G28 H28 (The evaluated cells)
6 5 2
The last occurrence of number 6 is was 2 days ago at G26, it is the earliest most recent days back occurrence of the numbers within F28, G28 and H28, then number 5 appears the next most recently back in cell H24, then number 2 is the furthest days back since it’s last occurrence, it’s last occurrence was in F20.
Because number 2 was furthest back, I want to list the number of rows since the occurrence of the furthest back of the numbers 6, 5 and 2 in the PD column, in this case cell C28 and I want the conditional format to update the color-bold-center as in the spreadsheet example. In this case the answer would be 8, because number was last occurred 8 cell rows ago and is the furthest back of the 3 cells evaluated.
I am still having problems with this original post. Can anyone give me assistance with this?
I think this works
Numcrun thanks for the help.
Your download version works.
It gives the correct results,
but the formula does not work
when I put it into my original chart.
I have attached another sample attachment, because I want this formula to calculate the result from every other role the same way you did in your spreadsheet sample, in otherwords, only the gray cells (F, G and H columns) are calculated in this way, while light-blue cells are ignored. When I used the formula it gave me a NAME? result.
Finally, how do I set-up a condition formula for every time a result is automatically entered into the PD column so that the cell is highlighted orange as in the sample.
OK I did a custom formula to compute this, so You'll have to import it into your spreadsheet. I assume you don't know VBA, so open the sheet I sent you and the sheet you want the formula in, press alt +F11 to open the VBA window. Find module 1 under 30603, right click it, choose export. Then right click on the name of your spreadsheet, and import module1.bas. Now the formula should work in your spreadsheet.
Ok hopefully this works. It puts 0 if it can't find a number. It searches 25? grey cells back I think, do you want 50?
Okay I imported the module, but got nothing but NAME. I imported the module from the latest file that you sent.
Yes, I do want to go back 50 gray spaces, but I think you have set that way.
I know I am missing something very minor.
what is it????
See chart attachment, for what I did
I just downloaded your sheet, and it works fine for me. What version of Excel do you have? I have Excel 2000. If you double click in C22 and press enter, do you get a number or #NAME?? I get a number as it should be. BTW, if you could give me a consistent version of the spreadsheet it would help, because with every different layout the code must be changed.
Numcrun
I tried it again and it worked, but as you stated with the updated sample I sent to you the results were incorrect. So the code would have to be updated. I do not know much about VBA, but I know a little about formulas. I have excel 2000 also. My actual spreadsheet, consist of several columns of data. The version I send to you will only represent the amount of columns separating the gray numerical digits cells from the PD column. This code approach works, but my only concern is my familiarity with it and not knowing the basic rules and parameters. Therefore, if it is a simple task maybe you can explain to me how you’ve updated the code should I have another change in cell location, then I could update the code myself. Otherwise, is there a way to do this using only Excel formulas to generate the same results. Keep in mind that I want to go back every 50 gray 3 digit cells to the PD column to calculate my result. Also, every time the corresponding 3 digit gray cells are entered the PD column is automatically updated with the results you been able to produce. I do appreciate you help.
This file seems to work now. I thought about doing it with a formula initially, but I think it's pretty much impossible. I used to use formulas for everything, but it's worth the effort to learn VBA. Anyway, to change the columns, go into the VBE (Alt + F11), then double click on module 1 on the left. In the code there are references to range("l" &i),range("m" & i), range("n" & i). Simply change the letters l,m,n to the columns the grey cells are in, if necessary.
Actually, pls ignore the previous file. This one updates automatically. Can I ask what this stuff does? Is it picking lotto numbers. Don't answer if you don't want to.
I might have changed some of the last few grey rows when testing - pls check.
Hoorray, we did it. Thanks for your help Numcrun. It works. and "yes" to your question.
I still have a little ways to go before I finish my project. I will be submitting a couple more post. The next one you may be able to help me with will be entitled "Longest Cells Out Analysis".
Thank you
Don’t have an account yet? Register yourself now and be a part of our community!
