Further help needed with my original request

[khanaran]

Thank you for the corrections and the formulae as well as the explanation. Real great work done by your Goodself and is much
appreciated.
I learnt a further step with reference to the information in I2
ie if I2 = 4 it has a combination with 8 from reftabl so N3 will read from the table and copy across

the combinations are as follows:-
4&8 / 8&4 5&8/8&5
2&7 /7&2

http://www.mediafire.com/file/6iqaqheorxk4525/datenumero2a.xlsx/file

kind regards

raj

Please excuse me for starting a new thread was not sure if I had to continue with previous thread

 

[Frank]

Thank you for the corrections and the formulae as well as the explanation. Real great work done by your Goodself and is much
appreciated.
I learnt a further step with reference to the information in I2
ie if I2 = 4 it has a combination with 8 from reftabl so N3 will read from the table and copy across

the combinations are as follows:-
4&8 / 8&4 5&8/8&5
2&7 /7&2

http://www.mediafire.com/file/6iqaqheorxk4525/datenumero2a.xlsx/file

kind regards

raj

Please excuse me for starting a new thread was not sure if I had to continue with previous thread

I'm sorry Raj, but you have not made it clear what processes lead to the combinations you mention. At the moment there appears to be ambiguity as to how you arrive at a combination of EITHER 8,4 OR 8,5 when cell N2 has a 4 in it. Which of the two is it ?
There has to be CLEAR rules to decide which. You have not specified what rule decides what PAIR of combinations is chosen for a specific value in I2.
I need to know a FULL list of what value appears in cell N3 for each value in cell N2 from 1 to 9, and which pair it then points to from this number.

I note that in your example that N3 also accesses reftable to pull out 17,26,35,44,53. Does this have any relevance to the pairs ??

You also have 3,6,9 ....6,3,9,...9,3,6 in a block below your pairs. This is confusing too.

Frank

 

[khanaran]

Hi Frank

Thank you for pointing out my mistake

I tried to explain what I need and will attach the file again

mediafire.com/file/6iqaqheorxk4525/datenumero2a.xlsx/file

Kind regards

Raj

 

[Frank]

Hi Frank

Thank you for pointing out my mistake

I tried to explain what I need and will attach the file again

mediafire.com/file/6iqaqheorxk4525/datenumero2a.xlsx/file

Kind regards

Raj

I'm sorry Raj, but you have not explained anything. I'm even more confused than I was before. Have you actually changed ANYTHING on that spreadsheet to make things clearer?

Can you explain what you think is "A combination". Do you mean the number has a partner in another table, the table you call "combination table" ?

so If I2 is either 4,8,2 or 7 then the number in the SECOND column of "Combinations" is returned to N3 ?? Is that what you mean ??

You need to explain this :-
"ie if I2 = 4 it has a combination with 8 from reftabl so N3 will read from the table and copy across".

Do you mean if I2=4 then from the second column of the (combination) table I need to take the value 8 and place it in N3 ?? Then refttable does the rest on line 3?

Are you saying theres only 4 pairs of combinations ? What if I2 is not 4,8,2, or 7 ?

You havent explained the 3,6,9 or the 6,3,9 or the 9,3,6 on the next 3 rows that I clearly asked you to explain. Why are they there ?

e.g
You need to explain your "combination table" more clearly. Why are there 4 rows of 2 numbers and three rows of three numbers. What am I supposed to do with this ?? What if I2 is 3,6, or 9 ?? what am I supposed to do then ??
If its 3, do I return 6 ? or do I return 9 ? its confusing.

You need to explain this statement :-
"If the value in I2 is 1 , it will read the value from reftabl and place in N2 to S2" NO you cannot do this, its a circular function .. you cant feed the result of a search or calculation back in to the cell that started it. Its an illegal call. N2 is already a FORMULA containing a RESULT.
Did you mean put it (number 1) and the results from reftable in cells I3 to S3 ? (instead of taking a number from the second column combinations table ?)

Have a long think. Imagine YOU are the programming the computer. How would you list the steps that are taken starting with a value in I2.
Next step .. do this ....IF this happens, do this or if that happens do something else
Next step..do this ....IF this happens, do this or if that happens do something else

No guesswork for computers , it all has to thought through and set in stone.

Frank

 

[khanaran]

Hi

I made the correction on the file and will greatly appreciate any advice and help hereto

http://www.mediafire.com/file/hpp02wty3mo059b/datenumero2correct.xlsx/file

thank you once again

Raj

 

[Frank]

Hi

I made the correction on the file and will greatly appreciate any advice and help hereto

http://www.mediafire.com/file/hpp02wty3mo059b/datenumero2correct.xlsx/file

thank you once again

Raj

Well that was much better, I think I understood that better. Im worried about your partner table with 4 columns where 3 has a partner with 3, this pulls up the same line from reftable as you already had!

The formula t add 1 year to a date is =DATE(YEAR(E2)+1,MONTH(E2),DAY(E2))

http://www.mediafire.com/file/i9hwn7xqcwqa45r/datenumero2correction.xlsx/file

Good luck!

 

[khanaran]

Hi Mr Frank

Thank you so much for solving my major problems

There is one more little issue that I am having with numerology and excel which I am using for the lotto

The problem that I am experiencing if the last two digits of the year-end in 00, the last digit should be replaced by 5. This does not make logical sense but in the numerology method that I am using this is a requirement when back tracking results. this is only for the last two digits of the year ends 00

This is a method used by QDimensy.

Thank you once again for all your help and advice and solving my problems

Regards

raj

 

[Frank]

Hi Mr Frank

Thank you so much for solving my major problems

There is one more little issue that I am having with numerology and excel which I am using for the lotto

The problem that I am experiencing if the last two digits of the year-end in 00, the last digit should be replaced by 5. This does not make logical sense but in the numerology method that I am using this is a requirement when back tracking results. this is only for the last two digits of the year ends 00

This is a method used by QDimensy.

Thank you once again for all your help and advice and solving my problems

Regards

raj

The problem I have with that, is that you are MANUALLY entering a date in cell E2.

so if you wanted to enter 25 June 2000 in cell E2, why dont you enter 25 June 2005 instead ?

Am I missing something ?

 

[khanaran]

Hi Mr Frank

So sorry I did not make myself very clear, my apologies


With numerology, this particular guide has some rules that make it a bit difficult to understand

Each year is separated into Y Y Y Y in separate cells

and the first two Y Y digits are added together and if the total is greater than 9 it is reduced to a single digit
eg 1 9 = 10 =1

the last two digits of the year are what we use in some calculation 9 9 = 18 =9
now if we copy this last two digits of the year into the next table, we add 1 to increase the year so 19 99+1
20 00, according to the rules which are not logical in the true sense, 00 is changed to 05.

Will it be possible to have the formula to do this.

Thank you for all the advice guidelines and help

Regards

raj

PS

I can if you need me to do some tables to show how it should be done, please let me know

 

[Frank]

Well without knowing in what cells you are performing this calculation in and where you are displaying the result I cannot give precise help.

In general if the result of a calculation in cell A1 becomes Zero and you want to alter this and display the amended version in another cell, say cell B1 then in cell B1 use the formula = IF(A1=0,5,A1). Will produce a 5 in cell B1 every time A1 is 0, otherwise it will copy the exact value of A1.

You can alternatively amend the result of the calculation in A1 before it is displayed there by adding an IF statement to the calculation being performed there.
In cell A1 :-
=IF(the calculation formula=0,5,the calculation formula)

Where you replace “the calculation formula” with the actual calculation expression that was in the cell A1 originally.

Try that, but in the actual cell you are using.

 

[khanaran]

Hi Mister Frank

Thank you so much for solving my requests and I apologise for not uploading the spreadsheet so as to highlight what help I need and to make myself clear.

All the help I received so far is helping me tremendously and I have learnt a great lot from your kindness

I need one final request for help with the attached sheet which I should have posted initially for a solution in place of beating around the bush

On the worksheet, the first date being the date of birth is copied into the first cycle table.

The second which is the current date, only the year copied into individual cells in table 1

Thereafter a formula needs to be entered in order to 1 copy the date of birth from table one and place in table 2 shown by "*" and the year shaded to be copied to table 2 increased by a year and likewise to table three until the last table.

In my two pyramids, when the tail end of the period comes to the second last two digits, a formula must copy the values of the two digits below the the last digit of the pyramid. This will also fluctuate depending on the length of the alphabets whic are reduced to numbers and furher reduced forming a pyramid

Trust my explanation explain the help I need

Mister Frank I don't how to thank you enough for all the times you came to my rescue and solved my problems for me

If there is any way that I can do something for you please let me know

Kind regards

raj

http://www.mediafire.com/file/amf9azbrv740x6r/datenumerocopy_and_paste_formula.xlsx/file

 

[Frank]

Hi Mister Frank

Thank you so much for solving my requests and I apologise for not uploading the spreadsheet so as to highlight what help I need and to make myself clear.

All the help I received so far is helping me tremendously and I have learnt a great lot from your kindness

I need one final request for help with the attached sheet which I should have posted initially for a solution in place of beating around the bush

On the worksheet, the first date being the date of birth is copied into the first cycle table.

The second which is the current date, only the year copied into individual cells in table 1

Thereafter a formula needs to be entered in order to 1 copy the date of birth from table one and place in table 2 shown by "*" and the year shaded to be copied to table 2 increased by a year and likewise to table three until the last table.

In my two pyramids, when the tail end of the period comes to the second last two digits, a formula must copy the values of the two digits below the the last digit of the pyramid. This will also fluctuate depending on the length of the alphabets whic are reduced to numbers and furher reduced forming a pyramid

Trust my explanation explain the help I need

Mister Frank I don't how to thank you enough for all the times you came to my rescue and solved my problems for me

If there is any way that I can do something for you please let me know

Kind regards

raj

http://www.mediafire.com/file/amf9azbrv740x6r/datenumerocopy_and_paste_formula.xlsx/file

Hi Raj,

Having seen your spreadsheet, something is not adding up. You say you are learning Excel and you ask me for help, yet your spreadsheet contains formulas of the type :- =SUMPRODUCT((MID($T13,COLUMN(OFFSET($A$1,0,0,1,LEN($T13))),1)=$A$1:$A$26)*$C$1:$C$26)

Did YOU write this? If you did perhaps you should be helping ME with Excel. Maybe you should tell me how the formula works.
There are also fairly advanced string handling formulas manipulating the dates like
=--MID(TEXT($AG$1,"yyyy"),COLUMNS($AG:AH),1). I've NEVER used string handling preceded by two dashes and dont even know what they are there for. However it seems to be working for the date of birth bit. I've left that unaltered since there is no arithmetic involved.

I'm wondering whose work I'm really correcting since you don't seem to know that to copy the contents of your two pyramid cells, you only need an equals sign (=M9) in M23 and =O19 in O23. A bit simpler than =SUMPRODUCT((MID($T13,COLUMN(OFFSET($A$1,0,0,1,LEN($T13))),1)=$A$1:$A$26)*$C$1:$C$26) I'm really puzzled. It doesn't make sense to me.

Anyway, the string handling was the wrong way to do it where adding a year was required, since you dont know when a 19 needs to become a 20 (thats 2 digits to change). I've changed all that to using the YEAR function which I explained to you right at the very beginning of this topic was necessary if you are doing arithmetic with years..
Also you mentioned something above about when the year ends in a 0, it had to change to end in a 5. However in your example you did not do that. Therefore my formulas don't do it either.

I think I've done enough of this now Raj, I wish you the best of luck.

http://www.mediafire.com/file/x6wycihqo4h7hvc/datenumerocopy%20and%20paste%20formula.xlsx

 

[Frank]

Corrected version uploaded (to same link) The other version did not daisy chain table to table , it added 1,2,3,4,5 years to each table based on current date which may seem like the same thing, but is subtly different. It becomes important if any dates in any table are amended because they end in a '0'. Presumably the 'next table would add 1 to the amended date ? However this issue was not clarified so formulas currently do not make this change.

 

[khanaran]

Hi Mister Frank

I must apologise for most of the formulas I extracted from a program on numerology and tried to use on my spreadsheet

I agree that firstly I am a little at loss with the formula.

Thank you for all your work

I am going to try and redo the spreadsheet.

I thank you for all the help and advice

regards

raj

 

[Frank]

Well I wish you the best of luck with it. I’m sorry I was unable to incorporate the “add 5 rule if ends in zero” rule. You did not tell me exactly where in that spreadsheet that rule applies, or whether any following tables also are affected by that digit change. Had you done that I might have been able to help you more.