+ Reply to Thread
Results 1 to 95 of 95

Help with formula

  1. #1
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Help with formula

    Thanks for helping. I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?

    2/18/1975, 156.7519, 175.8083, 235.4675, 253.765
    4/11/1973, 237,7642, 346.5342, 113.3145, 321.435
    5/12/1985, 342.7625, 123.4233, 253.4321, 357.234
    etc.
    etc.
    etc.

    Warmest Regards,
    Martin

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Thanks for helping. I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time?

    2/18/1975, 156.7519, 175.8083, 235.4675, 253.765
    4/11/1973, 237,7642, 346.5342, 113.3145, 321.435
    5/12/1985, 342.7625, 123.4233, 253.4321, 357.234
    etc.
    etc.
    etc.

    Warmest Regards,
    Martin
    Assuming that data is in A1:E3 (327.6742)

    in (say) G1 put

    =MOD(B1,15)

    and formula-fill this sideways and then bulk-fill downwards to cover all of your data.

    =SUMPRODUCT(--(G1:J3>=9.6)*(--(G1:J3<=9.8)))

    CTRL/Shift/Enter should count the items for you.

    hth
    ---

  3. #3
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Mod 15

    Thanks for helping, but my data is like this. I want to mod by 15. then once they have been mod 15, i would like excel to locate and highlight all the numbers in a range of say 9.6-9.9. I appreciate your help.
    A B C D E F
    1 Jul 3 1975 249.608 186.556 27.7985 240.2272 135.7637
    2 Jul 4 1975 249.586 186.5662 27.8225 240.2509 135.7736
    3 Jul 5 1975 22.1895 111.2196 208.358 249.5659 186.5761
    4 Jul 6 1975 208.358 249.5437 186.586 27.86544 240.2525
    5 Jul 7 1975 22.4237 111.4778 208.358 249.5228 186.5976

    Kindest Regards,
    Martin.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Thanks for helping, but my data is like this. I want to mod by 15. then once they have been mod 15, i would like excel to locate and highlight all the numbers in a range of say 9.6-9.9. I appreciate your help.
    A B C D E F
    1 Jul 3 1975 249.608 186.556 27.7985 240.2272 135.7637
    2 Jul 4 1975 249.586 186.5662 27.8225 240.2509 135.7736
    3 Jul 5 1975 22.1895 111.2196 208.358 249.5659 186.5761
    4 Jul 6 1975 208.358 249.5437 186.586 27.86544 240.2525
    5 Jul 7 1975 22.4237 111.4778 208.358 249.5228 186.5976

    Kindest Regards,
    Martin.
    so Mod by 15 as above, and select the range and 'Format', 'Conditional format', value is between, and set the format to the colour of your choice.

    ---

  5. #5
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Mod 15

    I have to appoligize. It has been along time since I used Excel and have forgotten almost all to do with it. I really hope you can help me with this. i have tried entering the function/MOD 15 and selecting the rows, but it dosent work. Can you give me a quick step by step???????????

    Really appreciate it,
    Martin.

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    I have to appoligize. It has been along time since I used Excel and have forgotten almost all to do with it. I really hope you can help me with this. i have tried entering the function/MOD 15 and selecting the rows, but it dosent work. Can you give me a quick step by step???????????

    Really appreciate it,
    Martin.
    I have no idea about the /Mod 15 function, all I can do is suggest what I said before which was
    Assuming that data is in A1:E3 (327.6742)

    in (say) G1 put

    =MOD(B1,15)

    and formula-fill this sideways and then bulk-fill downwards to cover all of your data.
    For help on Formula Fill you can see http://www.mvps.org/dmcritchie/excel/fillhand.htm

    hth
    ---

  7. #7
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Mod 15

    Awesome, Thanks Bryan I got the MOD to work. You said before to find the number in a range to use SUMPRODUCT. I dont want to count the occurences, I want Excel to find and maybe highlight them. Is that possible? say all number between 9.6 and 9.8.

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Awesome, Thanks Bryan I got the MOD to work. You said before to find the number in a range to use SUMPRODUCT. I dont want to count the occurences, I want Excel to find and maybe highlight them. Is that possible? say all number between 9.6 and 9.8.
    Yes, as per the prior post, on the area containing the Mod results,
    so Mod by 15 as above, and select the range and 'Format', 'Conditional format', value is between, and set the format to the colour of your choice
    ---

    so Mod by 15 as above, and select the range
    (highlight the cells concerened)

    and 'Format',
    (select 'Format' from the menubar)

    'Conditional format',
    (select 'Conditional Format' from the options)

    value is between,
    (select 'Value is Between')

    enter the lower value in the first value box (before the 'and'
    enter the higher value in the second value box (after the 'and'

    and set the format to the colour of your choice
    (Select the 'Format' button)
    select the 'Patterns' tab
    select a colour

    hth
    ---

  9. #9
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Helped

    Yes, it did help. Thanks very much. However, I can do the conditional format and find the entries I am looking for for each set. Eg. To find the most numbers within the set 0.0 - 0.3 I must do the conditional format and note how many. to do the set 0.1 - 0.4, same as above. Then 0.2 - 0.5, 0.3 - 0.6 and so on. The goal is to find what set or range has the most hits. I hope Excel can simplify the process, because doing each range takes a considerable amount of time as you can imagine. Is this possible? Would be very greatful if you could help with this? Thanks again for the mod bit, it really helped.

    Warmest Regards,
    Martin.

  10. #10
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Yes, it did help. Thanks very much. However, I can do the conditional format and find the entries I am looking for for each set. Eg. To find the most numbers within the set 0.0 - 0.3 I must do the conditional format and note how many. to do the set 0.1 - 0.4, same as above. Then 0.2 - 0.5, 0.3 - 0.6 and so on. The goal is to find what set or range has the most hits. I hope Excel can simplify the process, because doing each range takes a considerable amount of time as you can imagine. Is this possible? Would be very greatful if you could help with this? Thanks again for the mod bit, it really helped.

    Warmest Regards,
    Martin.
    Can you, within the set, calculate the low and high values into a cell on each row (applicable to that set)?
    Is so then you could use those cells to bulk-format the Conditional format.

    ---

  11. #11
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Thanks again

    The sets start at 0.0-0.3. The next set is 0.1-0.4. The next 0.2-0.5. I need to look at screens and of numbers and locate how many cells have numbers within the various sets. For example the following:
    0.4 0.5 0.4 0.2
    0.2 0.4 0.1 0.0
    0.8 0.4 0.3 0.5
    0.4 0.2 0.2 0.9
    0.3 0.8 0.6 0.2
    0.5 0.2 0.4 0.6
    0.9 0.1 0.4 0.8
    In the set 0.0-0.3 = 11 hits
    In the set 0.1-0.4 = 17 hits
    In the set 0.2-0.5 = 18 hits
    In the set 0.3-0.6 = 14 hits
    In the set 0.4-0.7 = 12 hits
    In the set 0.5-0.8 = 8 hits
    In the set 0.6-0.9 = 7 hits

    The sets go on upto 15.0. It would take along time to do it individually. I will if I have to, but I am really hoping Excel can help me. Is this possible?

    Kindest Regards,
    Martin.

  12. #12
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    I would count the number of occurences in each 0.1 range

    =SUMPRODUCT(--(G1:J3>=0.0)*(--(G1:J3<=0.1)))

    probably by putting the values in 2 columns from 0.0 to 15 in 0.1 increments. then sum each lot of 4 sum products, then choose the maximum of this column

    regards

    Dav

  13. #13
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Helping

    Thanks very much Dav, this will shave hours off. i did it and was able to figure out the application. I entered in the set 0.0-0.3 and counted the amount. which ever set had the highest, I used the conditional format to locate them. Great stuff. Many thanks to Bryan and Dav. Much appreciated. All the best to you both.

    Warmest regards,
    Martin.

  14. #14
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    an other thing

    I did everything and it worked great. I saved the data. I was hoping to leave the formulas in the cells and copy and paste new number and have the formulas automatically calculate. It did not happen. As soon as closed everything it saved it as a .asc file and when I opened it all the formulas had disappeared. The calculations remained, but no formulas? How can I redo the sheet and use it as a template like application, whereby I can copy and paste new data that the cells will auto calc?

    Warmest Regards,
    Martin.

  15. #15
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Ok now, but...

    Hi again, I realized I must save the file in .xls format for the formulae to stay.

    I have another prob, Iwas hoping to get solved. Lets say my data batch fills cells (A1:D10). I will use =SUMPRODUCT(--(A1:D10>=0)*(--(A1:D10<=0.3))) to count the number of hits with in the is range in this batch of data. If my next batch of data runs (A1:D5) only. Then the formula sumproduct above will count the cells that do not have a number as zero and throw of the hit counts to the set 0.0 - 0.3. Is there something I can include so excel will not count cells that have nothing entered? the idea is I can leave the sumproduct formula and copy paste different batches of data without adjusting them to the parameters of (A1:D10).

    Warmest regards,
    Martin.

  16. #16
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi again, I realized I must save the file in .xls format for the formulae to stay.

    I have another prob, Iwas hoping to get solved. Lets say my data batch fills cells (A1:D10). I will use =SUMPRODUCT(--(A1:D10>=0)*(--(A1:D10<=0.3))) to count the number of hits with in the is range in this batch of data. If my next batch of data runs (A1:D5) only. Then the formula sumproduct above will count the cells that do not have a number as zero and throw of the hit counts to the set 0.0 - 0.3. Is there something I can include so excel will not count cells that have nothing entered? the idea is I can leave the sumproduct formula and copy paste different batches of data without adjusting them to the parameters of (A1:D10).

    Warmest regards,
    Martin.
    A self-adjusting Indirect Sumproduct, - I could not get that to work dynamically, but did with Helper columns as in http://www.excelforum.com/attachment...2&d=1160955999 in http://www.excelforum.com/showthread.php?t=578161

    Perhaps this could give you some ideas?

    ---

  17. #17
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi again, I realized I must save the file in .xls format for the formulae to stay.

    I have another prob, Iwas hoping to get solved. Lets say my data batch fills cells (A1:D10). I will use =SUMPRODUCT(--(A1:D10>=0)*(--(A1:D10<=0.3))) to count the number of hits with in the is range in this batch of data. If my next batch of data runs (A1:D5) only. Then the formula sumproduct above will count the cells that do not have a number as zero and throw of the hit counts to the set 0.0 - 0.3. Is there something I can include so excel will not count cells that have nothing entered? the idea is I can leave the sumproduct formula and copy paste different batches of data without adjusting them to the parameters of (A1:D10).

    Warmest regards,
    Martin.
    =SUMPRODUCT(--(A1:D10<>"")*(--(A1:D10>=0)*(--(A1:D10<=0.3))))

    should work
    ---
    Si fractum non sit, noli id reficere.

  18. #18
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Like a Charm

    Thanks very much. Your awesome Bryan. Works beautifully. All the best to you.

    Sincerely,
    Martin.

  19. #19
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Thanks very much. Your awesome Bryan. Works beautifully. All the best to you.

    Sincerely,
    Martin.
    Good to see Martin, and thanks for the response.
    ---

  20. #20
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Another thing

    Hi again Brian, now I am at the next step. Is it possible, now I have all the data MOD15, then I have found all the hits that occur between,
    say, 2.2 - 2.5. Is there a way of finding out what the sum of all these hits is, divide the sum by the number of hits to get a middle average?
    I can use the conditional format to locate and highlight the hits. Then I use my calculator to add them up, divide by the number to get the average. Can Excel do the this for me too?

    Kindest Regards,
    Martin.

  21. #21
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi again Brian, now I am at the next step. Is it possible, now I have all the data MOD15, then I have found all the hits that occur between,
    say, 2.2 - 2.5. Is there a way of finding out what the sum of all these hits is, divide the sum by the number of hits to get a middle average?
    I can use the conditional format to locate and highlight the hits. Then I use my calculator to add them up, divide by the number to get the average. Can Excel do the this for me too?

    Kindest Regards,
    Martin.
    If you can formula-find them to highlight then you can go to some spare cells (say, columns F to to I) and extract all required numbers - then you can add them etc at will.

    For A1:D10 use F1:I10 with (in F1) =If(And(A1>=2.2,A1<=2.5),A1,"") which you can formula-fill sideways and then downwards to extract the whole block. Repeat in columns K:N for other ranges.

    use Count - CountBlank if needed.

    See how you go - and let me know what happens.

    ---

  22. #22
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Helped

    Hi Bryan, thanks again. i did get it to work, but it is the same as conditional format, and highlighting the numbers then visually scanning the sheet for the numbers and add them. The difference with your formula is it that I will still have the same size sheet, but only the numbers I require will show up. is there a way to have excel, extract using the formula you gave me,add them together, divide by the number of numbers, which will give me an average?

    Kindest Regards,
    Martin.

  23. #23
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan, thanks again. i did get it to work, but it is the same as conditional format, and highlighting the numbers then visually scanning the sheet for the numbers and add them. The difference with your formula is it that I will still have the same size sheet, but only the numbers I require will show up. is there a way to have excel, extract using the formula you gave me,add them together, divide by the number of numbers, which will give me an average?

    Kindest Regards,
    Martin.
    The formula can be modified to extract any range, into any 'area', just enter the formula, -fill right and down. You can Count, Sum and divide all of these figures with no problem.

    If you have troubles just copy the sheet and post it here.

    ---

  24. #24
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Help

    If my data spans A1:D20, in E1 I would enter the last formuala you gave me and fill this down and across. I have to fill the same amount of cells as A1:D20 to get the hits to the range. Then I have to add them. My problem comes in when I have a data sheet that runs A1:D500. If after finding out I have 25 hits to the range of 2.2-2.5. I need to formula fill the space E1:H500, then scroll up and down to find the 25 hits, then add them, divide by 25 to get the average. Is there a way for Excel to this for me?

    Kindest Regards,
    Martin.

  25. #25
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    If my data spans A1:D20, in E1 I would enter the last formuala you gave me and fill this down and across. I have to fill the same amount of cells as A1:D20 to get the hits to the range. Then I have to add them. My problem comes in when I have a data sheet that runs A1:D500. If after finding out I have 25 hits to the range of 2.2-2.5. I need to formula fill the space E1:H500, then scroll up and down to find the 25 hits, then add them, divide by 25 to get the average. Is there a way for Excel to this for me?

    Kindest Regards,
    Martin.
    Yes, get excel to Count them.

    ---

  26. #26
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Help

    If I have data in A1:D500, do I have to formula fill E1:E500? If I have 25 hits to the range in question, I have to scan the 500 cells for the hits? and what would I enter to have Excel, add them and divide by the number to get an average?

    Kindest Regards,
    Martin.

  27. #27
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    If I have data in A1:D500, do I have to formula fill E1:E500? If I have 25 hits to the range in question, I have to scan the 500 cells for the hits? and what would I enter to have Excel, add them and divide by the number to get an average?

    Kindest Regards,
    Martin.
    If you have 4 columns wide and 500 rows deep you need to enter the formula, fill 3 right, and bulk fill( all 4 columns) 499 down

    and then count them
    =Count(E1:H500)

    and sum them

    =Sum(E1:H500)

    and then divide one of thos answers into the other to get the average.

    ---

  28. #28
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    helped again

    It worked. Thanks again Bryan. I am at the end of my research. You have helped me tremendously. Thanks again and all the best.

    Cheers,
    Martin.

  29. #29
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Needed again

    Hi Bryan, I figure why stop now. If I have a cell that has the following number; 2.6745 and I what this number expressed in time. The number is in cell D5, what formula can I use to do it?

    Kindest Regards,
    Martin.

  30. #30
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan, I figure why stop now. If I have a cell that has the following number; 2.6745 and I what this number expressed in time. The number is in cell D5, what formula can I use to do it?

    Kindest Regards,
    Martin.
    What does the 2.6745 represent? -
    2.6 hours?
    2.6 minutes?

    To Excel it looks like 4:11:17pm on 2/Jan/1900

    If it represents 2.67 hours, format a cell to Custom h:mm
    and set it
    =A1/24

    etc.

    ---

  31. #31
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Time Help

    Bryan, 2.6478 would have to be diplayed as 2:38
    14.4573 would be 14:27.
    0.4573/10x6=27

    Cheers Martin.

  32. #32
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Bryan, 2.6478 would have to be diplayed as 2:38
    14.4573 would be 14:27.
    0.4573/10x6=27

    Cheers Martin.
    . . . and did the formula display that?

    If it represents 2.67 hours, format a cell to Custom h:mm
    and set it
    =A1/24
    where A1 represents where the current figure is.

    If you are calculating the figure in the cell that you want the display then (as stated) format that cell to Custom h:mm and use

    =(your.current.calculation)/24

    hth
    ---

  33. #33
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    2 Question post

    Bryan when I entered the calculation the result was 3.something. If I have in a cell with the nimber 7.2864 this equals = 7degrees17minutes which is (0.2864/10x6) When Excel does it it divides the whole number too, hence I get (7.2864/10x6) which is incorrect = 4:37.

    2/ I have 3 columns of data that list the ranges and one column that shows how many hits to the various ranges. How can I sort these columns so that I can view the range that got the most hits? ie:

    Range Hits
    0.2 - 0.4 1
    0.4 - 0.6 2
    0.6 - 0.8 3
    0.8 - 1.0 1
    1.0 - 1.2 3
    1.2 - 1.4 4
    1.4 - 1.6 3

    The problem is that when I goto sort the columns it throws of the ranges, I want the range to carry with the hits. Is this possible?

    Cheers Martin.

  34. #34
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    I will try to guess what you mean, for the message beats me.

    Quote Originally Posted by Martindelica
    Bryan when I entered the calculation the result was 3.something.
    What calculation? - and what should it have been if not 3.something?
    If I have in a cell with the nimber 7.2864 this equals = 7degrees17minutes which is (0.2864/10x6) When Excel does it it divides the whole number too, hence I get (7.2864/10x6) which is incorrect = 4:37.
    ok - that's the 'Dutch' version, I'll just wait for the movie (ie, I have no idea what you said)
    2/ I have 3 columns of data that list the ranges and one column that shows how many hits to the various ranges. How can I sort these columns so that I can view the range that got the most hits? ie:

    Range Hits
    0.2 - 0.4 1
    0.4 - 0.6 2
    0.6 - 0.8 3
    0.8 - 1.0 1
    1.0 - 1.2 3
    1.2 - 1.4 4
    1.4 - 1.6 3

    The problem is that when I goto sort the columns it throws of the ranges, I want the range to carry with the hits. Is this possible?

    Cheers Martin.
    Just sort the data over the 'Hits' column?

    note, when you sort you must select the whole row (preferably the whole sheet) and sort using the Hits column as the first key.

    Whereever you had the 2.6478 figure, did that show correctly as 2:38 in time ?

    ---

  35. #35
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Lol

    Sorry, please forget I mentioned the time thing. The problem of sorting is taxing me heavily;

    I have a simple formula in the range column that adds 0.2 to the previous cell and I filled the cells to give me the ranges as opposed to entering each one. When I sort I get a lot of ###REF cells. I try to copy and paste the columns to new cells but the formulas go too. The hits column has no formula so it sorts ok, but the other column has a formula, and gives the ###REF error.

    Cheers Martin.

  36. #36
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Sorry, please forget I mentioned the time thing. The problem of sorting is taxing me heavily;

    I have a simple formula in the range column that adds 0.2 to the previous cell and I filled the cells to give me the ranges as opposed to entering each one. When I sort I get a lot of ###REF cells. I try to copy and paste the columns to new cells but the formulas go too. The hits column has no formula so it sorts ok, but the other column has a formula, and gives the ###REF error.

    Cheers Martin.
    Before sorting the formula column do Copy (of the column) and Paste Special = Values back over itsself

    (because when you sort, then the 'previous row' aint 'previous' anymore).
    ---

  37. #37
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Helped again

    Bryan, you are awesome. Thankyou very much. It worked and I have learnt alot about excel working your formulas into my sheets. Thanks again.
    All the best to you.

    Sincerely,
    Martin.

  38. #38
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Question

    Bryan is there any way to lock my formulas into the sheet. i will be copying and pasting data in and out and was wondering the chances of an error would be high. If I have a column with formulas in it, how can I lock them?

    Kindest Regards,
    Martin.

  39. #39
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    All figured out

    Thanks Bryan, I got my sheet all locked and hidden. The only problem I have now is watching I dont double click a cell that is locked/hidden so the information doesn't disappear. I can live with this, but if there is a fix for that, do tell.
    Once again, thanks Bryan, I have learnt even more and wish you all the best.
    Martin.

  40. #40
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Thanks Bryan, I got my sheet all locked and hidden. The only problem I have now is watching I dont double click a cell that is locked/hidden so the information doesn't disappear. I can live with this, but if there is a fix for that, do tell.
    Once again, thanks Bryan, I have learnt even more and wish you all the best.
    Martin.
    Martin,
    the only real cure for data loss is a backup copy, have a spare folder and SaveAs a new version (I just add a letter to the file's name, so TestA.xls through TestZ.xls are my 'saves'). If you try to Paste to a locked cell the clipboard should still be available to Paste to the correct cell or range after you clear the error message.

    I presume that you Unlocked the cells (columns) that you would normally Paste to, in (rightmouse) Format Cells, Protection, untick the 'Locked')

    ---

  41. #41
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Thanks Bryan

    For some reason this weekend I found it difficult to get through to the site. Anyway, okay now. I have done everything you told me, and it has worked beautifully, thank you very much for your help. All the best.

    Warmest Regards,
    Martin.

  42. #42
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Pursuit of Perfection

    Hi Bryan back again. I have been working with the sheet and it rocks. I want to tweak it a little now.
    I'll try for the english version:
    I have data in cells A1:G50. they are numbers in decimals. Remember I have the ranges of 0.0-0.2, 0.2-0.4, 0.4-0.6...Well I have these ranges in column and the number of hits to each range beside its range. If I want excel to go throught the data and count and sum all the numbers between 0.4-0.6 and averga that number can this number be put in a single cell?

    Range Hits Average
    0.0 - 0.2 26 0.1435
    0.2 - 0.4 13 0.2457

    The average cell, would have to first sum all the numbers within a specified range and averge the sum.

    Cheers, Martin.

  43. #43
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan back again. I have been working with the sheet and it rocks. I want to tweak it a little now.
    I'll try for the english version:
    I have data in cells A1:G50. they are numbers in decimals. Remember I have the ranges of 0.0-0.2, 0.2-0.4, 0.4-0.6...Well I have these ranges in column and the number of hits to each range beside its range. If I want excel to go throught the data and count and sum all the numbers between 0.4-0.6 and averga that number can this number be put in a single cell?

    Range Hits Average
    0.0 - 0.2 26 0.1435
    0.2 - 0.4 13 0.2457

    The average cell, would have to first sum all the numbers within a specified range and averge the sum.

    Cheers, Martin.
    =SUMPRODUCT(--(A1:G50>=0.4)*(--(A1:G50<=0.6))*(A1:G50))/SUMPRODUCT(--(A1:G50>=0.4)*(--(A1:G50<=0.6)))

    should work
    ---

  44. #44
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Awesome

    Hi Bryan, it worked Thanks, but can I fix it so that it does not count a blank cell as "0" in the formula. Aso can I fix it to display the answer to 3 deimal places?

    Cheers, Martin.

  45. #45
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan, it worked Thanks, but can I fix it so that it does not count a blank cell as "0" in the formula. Aso can I fix it to display the answer to 3 deimal places?

    Cheers, Martin.
    ?

    Blank cells should not pass the test >=0.4

    Do they?

    Format the cell to as many decimal places as are required.

    hth
    ---

  46. #46
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    ranges

    Bryan the ranges go from 0.0 to 0.2, o.2 to 0.4....Only the cell that has 0.0-0.2 has manuy hits if there are blank cells.
    I figured out the decimal, thanks.

    Martin.

  47. #47
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Bryan the ranges go from 0.0 to 0.2, o.2 to 0.4....Only the cell that has 0.0-0.2 has manuy hits if there are blank cells.
    I figured out the decimal, thanks.

    Martin.
    Yes, 0 was requested.

    ranges go from 0.0 to 0.2
    You want to change the range to 0.00001 to 0.2 ?

    Just amend te formula that you are using.

    If you are using the formula

    =SUMPRODUCT(--(A1:D10<>"")*(--(A1:D10>=0)*(--(A1:D10<=0.3))))

    then just omit the = for the >=0

    hth
    ---

  48. #48
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Back for Bryan

    Hi Bryan, long time. Hope your well. Gotta another for ya! If I have two columns of data and I want to find out when they are both exactly 9 apart on the same day, is there a formula that will compare the two columns and highlight the days when the two columns are 9 apart?

    Jan 2 45.987 48.346
    Jan 3 28.675 46.736
    Jan 4 68.987 54.978
    Jan 5 80.346 89.453 This would be a hit rounded up, 89 - 80 = 9
    Jan 6 94.893 32.784
    Jan 7 67.384 32.987

    All the Best,
    Martin.

  49. #49
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan, long time. Hope your well. Gotta another for ya! If I have two columns of data and I want to find out when they are both exactly 9 apart on the same day, is there a formula that will compare the two columns and highlight the days when the two columns are 9 apart?

    Jan 2 45.987 48.346
    Jan 3 28.675 46.736
    Jan 4 68.987 54.978
    Jan 5 80.346 89.453 This would be a hit rounded up, 89 - 80 = 9
    Jan 6 94.893 32.784
    Jan 7 67.384 32.987

    All the Best,
    Martin.
    Just Format, Conditional Format the two columns, and set Formula

    =($A1-$B1)=9

    set the highlight colour.

    A and B will need to be your columns, retain the $ to highlight in both cells.

    hth
    ---

  50. #50
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Formula

    Bryan sorry although I said the number was nine, it is actually 9 is the root. So when the two coloumns are 9, 18, 27...exactly apart. Is this possible?

    Martin.

  51. #51
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Try:

    =MOD($A1-$B1,9)=0
    Mangesh

  52. #52
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Formual

    I there, I tried the MOD($A1-$B1)=0 and it returns FALSE. Thanks for trying.

    Martin.

  53. #53
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    I there, I tried the MOD($A1-$B1)=0 and it returns FALSE. Thanks for trying.

    Martin.
    with the = sign?

    it gives False if you enter as a formula

    Put it in the Formula part of Format, Conditional Formatting

    =MOD($A1-$B1,9)=0

    ---
    Last edited by Bryan Hessey; 11-07-2006 at 07:42 AM.

  54. #54
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Helping out

    Hi there guys, I tried the format, conditional format Bryan, but it did not alter or change the numbers at all or highlight them. Here is an actual set of numbers and some are on the same root of 9 axis;

    177.7454 114.7428
    196.5276 115.4827
    192.0798 111.0843
    189.7349 117.7409
    196.6298 124.6055
    212.0834 131.0206
    220.8327 130.7211
    226.9009 127.8837
    224.1093 125.0836
    219.2574 129.2465

    219.2183 129.3572
    219.1816 129.4687
    219.1482 129.5813
    219.1178 129.6947
    219.0903 129.8077
    219.0659 129.9221

    These are just some that are on the same root of 9 axis.
    Thanks, Martin.

  55. #55
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi there guys, I tried the format, conditional format Bryan, but it did not alter or change the numbers at all or highlight them. Here is an actual set of numbers and some are on the same root of 9 axis;

    177.7454 114.7428
    196.5276 115.4827
    192.0798 111.0843
    189.7349 117.7409
    196.6298 124.6055
    212.0834 131.0206
    220.8327 130.7211
    226.9009 127.8837
    224.1093 125.0836
    219.2574 129.2465

    219.2183 129.3572
    219.1816 129.4687
    219.1482 129.5813
    219.1178 129.6947
    219.0903 129.8077
    219.0659 129.9221

    These are just some that are on the same root of 9 axis.
    Thanks, Martin.
    Martin, you have a strange calculation to give those as 'root 9'

    Try for the Conditional Format: (select Columns A & B etc)

    formula is

    =MOD(INT($A1-$B1+0.01),9)=0

    and set your pattern.

    see how that goes for you.

    ---

  56. #56
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    More help

    Hi Bryan, I have not tried the formula, but will asap Thanks. I have a new problem. I hope I can explain this. If you can email me privately at [email protected], we can discuss this.
    Basically, I have a set of data that has dates with numbers. I want to plot these numbers on a chart. Then I want to be able to average them in gropus of 3 and plot those results. Eg
    Jan 1 4523
    Jan 26 2645
    Feb 21 6876
    Mar 3 4786

    This I would plot as is. Then in chart b, I would take the number of days between jan 1 and feb 21, avegare them as well as avergae their numbers and plot this new number as the avergae date and the result. then i want to see the percentage difference the average is from the actual and plot the up or down.

    Too much?
    Can this be done?

    Cheers, Martin.

  57. #57
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan, I have not tried the formula, but will asap Thanks. I have a new problem. I hope I can explain this. If you can email me privately at [email protected], we can discuss this.
    Basically, I have a set of data that has dates with numbers. I want to plot these numbers on a chart. Then I want to be able to average them in gropus of 3 and plot those results. Eg
    Jan 1 4523
    Jan 26 2645
    Feb 21 6876
    Mar 3 4786

    This I would plot as is. Then in chart b, I would take the number of days between jan 1 and feb 21, avegare them as well as avergae their numbers and plot this new number as the avergae date and the result. then i want to see the percentage difference the average is from the actual and plot the up or down.

    Too much?
    Can this be done?

    Cheers, Martin.
    Martin,

    If the first set of data is Jan 1 to Feb 21, is the second set Jan 23 to Mar 3, or Mar 3 to whereever?

    ---

  58. #58
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    help

    Thanks.
    Lets try it this way.
    Jan 1 3562
    Feb 1 6736
    Mar 1 3756
    Apr 1 2867
    May 1 7564
    June 1 3476
    I would plot these in chart 1. O on the x and z axis and run along the bottom the months.
    Chart 2 would take the sum of jan 1, feb 1 and mar 1 (3562+6736+3756 / 3) and plot this result for Feb 1. For Mar 1 we would add feb1, mar 1 and apr 1/3 and plot this for Mar 1...
    Chart 3 would plot the percentage each month was above or below the actual.

    Martin.

  59. #59
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Thanks.
    Lets try it this way.
    Jan 1 3562
    Feb 1 6736
    Mar 1 3756
    Apr 1 2867
    May 1 7564
    June 1 3476
    I would plot these in chart 1. O on the x and z axis and run along the bottom the months.
    Chart 2 would take the sum of jan 1, feb 1 and mar 1 (3562+6736+3756 / 3) and plot this result for Feb 1. For Mar 1 we would add feb1, mar 1 and apr 1/3 and plot this for Mar 1...
    Chart 3 would plot the percentage each month was above or below the actual.

    Martin.
    something like the attached?

    just noticed, you need to extend the category labels on the first chart to row 7

    also, on the Percentage chart, you might like the Gridline set to 'Category x crosses at Zero' to give the 'above and below' - then push the labels down to +1000 offset


    ---
    Attached Files Attached Files
    Last edited by Bryan Hessey; 11-19-2006 at 01:31 PM.

  60. #60
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    help

    Bryan, this is the formula i need to come up with. See attached.

    Cheers, Martin.

  61. #61
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    help

    file too big. can i email it

  62. #62
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    file too big. can i email it
    Yes,

    as noted.
    ---

  63. #63
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    help

    Bryan can you email me and I can reply to your address?

    Cheers.

  64. #64
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Bryan can you email me and I can reply to your address?

    Cheers.
    My email is in your private messages

  65. #65
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Hi Bryan

    Seasons greetings my friend. You know I just logged in the other day and found out you had post your email privately. I had no idea I had a private mail. Awesome, thanks buddy. I sent you an email the other day, did you get it?

    Kindest Regards,
    Martin.

  66. #66
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Awesome, I sent you an email the other day, did you get it?

    Kindest Regards,
    Martin.
    I did now, and a reply was sent. btw, where's the Moon? is that the retrograde True Node?

    ---
    Last edited by Bryan Hessey; 12-24-2006 at 02:37 PM.

  67. #67
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Bryan

    Hi Bryan,so far the program works beautifully. I sent you another file that needs your magic touch. Last one. I have a real big gift for you buddy!!!! Real nice!!!

  68. #68
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi Bryan,so far the program works beautifully. I sent you another file that needs your magic touch.
    Martin,

    the date formula you already have, put in A3

    =A2+1

    and formula fill that to row 366, then any date entered in A2 will populate the column.

    The other numbers shown are incorrect, more especially for the 48-segment-offset-by-one-segment used to produce 24 segments rounded.

    Between the selection and the next possible selection, or the selection and the immediate prior selection, there is, for some columns, a variance of between -1.26 to 2.17 both extremes being from figures in column Y, however as the figures shown in A2:S2 bear no relationship to those shown in X2:AG300+ the effect of this is not easy to see, but a variance of 1.5 on the lower green line will exist.

    Is this (Now shown) Sheet1 to be automatically produced from the figure supplied in th '48 version Sheet2 at O1 ?

    Columns A C E G (ie, every second column) would seem to be superfluous, the numbers shown there are calculatable from the Row() number, and are incorrect anyway.

    Do you want to retain these for any purpose?
    Do you want to include the variance as above?
    Do you want to retain the true value and display only the whole number?
    (truncating at this point could introduce further inaccuracies if the figures are used for further calculations).

    ---

  69. #69
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Response

    Hi buddy, on sheet 2 if I enter the date the data for that date should be rounded and then ploted in sheet 1 for the corresponding planet. As we go up each line will simply add 10 to that rounded longitude.
    Eg we have sun at 220 longitutde, then the 90 line will be 220+90 all the way to 360, which would be 220+360 and shown as 580 on the 360 line.

    Warmest Regards,
    Martin.

  70. #70
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Ps

    Also, no I dont need the figures after this. This is awesome, you have made my job so much easier. Thanks you so much. I look forward to seeing the finished product. I think there is nothing you cant do. Awesome buddy I owe you lots for this.

  71. #71
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi, on sheet 2 if I enter the date the data for that date should be rounded and then ploted in sheet 1 for the corresponding planet. As we go up each line will simply add 10 to that rounded longitude.
    Eg we have sun at 220 longitutde, then the 90 line will be 220+90 all the way to 360, which would be 220+360 and shown as 580 on the 360 line.

    Warmest Regards,
    Martin.
    it should be as per the last example, ignore the first line (to be +10 to +360) or last line (to be +0 to +350) in Sheet1 as required.

    Use 'Hide' on the row or colour it blue, but do not delete it.

    hth
    ---

  72. #72
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    You did it again!!!

    Awesome job buddy. It works like a charm. Bryan how can delete Earth, Chiron and Transpluto without knocking out the formula? Also how do I resize the cells like before you had them very small?

    Kindest Regards,
    Martin.

  73. #73
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Ps

    Also in S:1 you wrote the following formula: '=Round, RoundN50 or RownDown
    is the spelling correct; RownDown or should it be RoundDown? Can I change it?

  74. #74
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Also in S:1 you wrote the following formula: '=Round, RoundN50 or RownDown
    is the spelling correct; RownDown or should it be RoundDown? Can I change it?
    You can delete S1 & T1, there is only Rounddown, and that is not an option.

    To adjust column size select the right-side bar of the column letter, and either drag left-right, or rightmouse and select Column Width.

    For non-required items, rightmouse the column and Hide.

    They will remain in the count. If any column is deleted the formula will all turn #REF as they are all relative formula.

    ---

  75. #75
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Thanks Bryan

    Byran the problem is when I hide Earth, Chiron and Transpluto they are still reflected in my Max count for the given day. I realize now they are insignifcant and need to delete those three. How can I do that without affecting the others?

    Kindest Regards,
    Martin.

  76. #76
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Byran the problem is when I hide Earth, Chiron and Transpluto they are still reflected in my Max count for the given day. I realize now they are insignifcant and need to delete those three. How can I do that without affecting the others?

    Kindest Regards,
    Martin.
    I guess, simply adjust the formula for t he new column counts, but you will need to adjust all columns from B to BF and re-fill the formula down the column as required.

    ---

  77. #77
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Thanks

    Hi there, Bryan I emailed the file to you. What would the formula be if I had to do it myself? Sorry for the trouble, but I really do appreciate the help. I could not have done this without your help. All the sheets you have done, will result in saving my countless hours of work. Thanks soooo much buddy.

    Warmest Regards,
    Martin.

  78. #78
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Hi there, Bryan I emailed the file to you. What would the formula be if I had to do it myself? Sorry for the trouble, but I really do appreciate the help. I could not have done this without your help. All the sheets you have done, will result in saving my countless hours of work. Thanks soooo much buddy.

    Warmest Regards,
    Martin.
    Martin,

    I have removed the 3 columns from the book I last sent to you, so that there are no errors.

    In sheet2 (was O1, now L1) the date, you moved the date and put 'dd/mm/yy' - that is incorrect.

    The formulae you are using on sheet1 are bad practice, the ones currently there should be ok (but hey, it's your book, if you want to change them go ahead)

    The date thing was still wrong though.

    Cheers.
    ---

  79. #79
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Help

    627 627 645 603 590
    360 637 637 655 613 600
    CST+6 647 647 665 623 610

    This is on sheet 1. Can I move the CST+6 and put the 360, because the longitude of 287 plus 360 = 647

  80. #80
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212
    when you say the date is wrong dd/mm/yyyy do you mean moving it is wrong or the format is wrong? Can I write dd/mm/yyyy below.

  81. #81
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Hi Bryan

    Hey buddy, I sent you another email. Got the progam and it looks great. Again you have done it. Great job, man.

  82. #82
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    when you say the date is wrong dd/mm/yyyy do you mean moving it is wrong or the format is wrong? Can I write dd/mm/yyyy below.
    No - don't write dd/mm/yy because it isn't - it just appears that way.

    ---

  83. #83
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    bryan

    Bryan, how can dd/mm/yyyy be wrong? I am confused. Is it dd/mm/yyyy or mm/dd/yyyy? and why cant I put that in the cell below?
    Sorry buddy, but I am still in kindergarten when it comes to Excel.

    Cheers,
    Martin.

  84. #84
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Sorry Buddy

    I resent the file...dont know what happened there, but scanned and cleaned. ok now.

  85. #85
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Bryan, how can dd/mm/yyyy be wrong? I am confused. Is it dd/mm/yyyy or mm/dd/yyyy? and why cant I put that in the cell below?
    Sorry buddy, but I am still in kindergarten when it comes to Excel.

    Cheers,
    Martin.
    Martin,

    it's NOT dd/mm/yy


    it's a DATE

    a 5v6 number

    32000.000000 (or thereabouts)

    it has to be the same format as column A or your sheet will fall down.

    They appear to you as dd/mm/yy and, to an American system, as mm/dd/yy because that is the want of Americans.

    There's really no other way I can say it, except DON'T change it if you want the sheet to work.

    It appears dd/mm/yy to you

    it is NOT dd/mm/yy

    don't put 'dd/mm/yy' as the sheet will then fail, and you may not know that you received 4th March on 3rd April.


    hth
    ---

  86. #86
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Thanks

    I am not formating the cell only typing it in as a reminder to me. Is that okay?
    I re-sent that other file, hopefully no probs.

    Kindest Regards and may you have a happy and prosperous 2007,
    Martin.

  87. #87
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    I am not formating the cell only typing it in as a reminder to me. Is that okay?
    I re-sent that other file, hopefully no probs.

    Kindest Regards and may you have a happy and prosperous 2007,
    Martin.
    Martin,

    it's a Sheet full of Alpha-wotnot characters that I would not have an idea what you are trying to do with.

    Is there any clues?

    ---

  88. #88
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Reply

    hi there;
    What the sheet does is assigns values to specific aspects on specific days. A conj is 10 *** is 3 and Trine is 10. These a positive. The negatives are the Square 8 and the opp 6.
    Eg; On January 3 2007 we have;
    Conj *** Trine Total Square Opp Total Day
    2 1 3 53 3 2 36 53-36 = +17

    This would be a green day.
    The Conj(10)x2=20, ***(3)x1=3, Trine(10)x3=30 Total 53
    The Square(8)x3=24, Opp(6)x2=12 Total 36
    The difference is 17. If this could be plotted as like sine wave or a bar chowing which is higher the positives or the negatives, it would be fantastic.

    Kindest Regards,
    Martin.

  89. #89
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Ps

    the numbers did not come out right,here they are again.
    The value for a conj is 10, the *** is 3, the Trine is 10.
    the negatives are the Square value is 8 and the opp is 6. You also have to have the sheet take into account the different dates in the 4 columns of the different charts. Can this be done?

    Martin.

  90. #90
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    hi there;
    What the sheet does is assigns values to specific aspects on specific days. A conj is 10 *** is 3 and Trine is 10. These a positive. The negatives are the Square 8 and the opp 6.
    Eg; On January 3 2007 we have;
    Conj *** Trine Total Square Opp Total Day
    2 1 3 53 3 2 36 53-36 = +17

    This would be a green day.
    The Conj(10)x2=20, ***(3)x1=3, Trine(10)x3=30 Total 53
    The Square(8)x3=24, Opp(6)x2=12 Total 36
    The difference is 17. If this could be plotted as like sine wave or a bar chowing which is higher the positives or the negatives, it would be fantastic.

    Kindest Regards,
    Martin.
    Martin,

    Your idea sounds good, but I think you should open a new post with this, to see if anyone understands enough of what you are seeking to be able to help you.

    Bryan
    ---

  91. #91
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Hi there

    Come on buddy, if there is anybody that do this it's you. Can you get me started and I can see how we go. Can excel read text or shoiuld I change the text to a value, like SUN equals 1, Mars equals 2 ... and a conj is 12, *** is 13....

    Kindest, Warmest, Happiest, Prosperousest Regards,
    Martin.

  92. #92
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Come on, if there is anybody that do this it's you. Can you get me started and I can see how we go. Can excel read text or shoiuld I change the text to a value, like SUN equals 1, Mars equals 2 ... and a conj is 12, *** is 13.... Martin.
    Martin,

    What you need is someone who can understand at least the basics, if not the complexities, of taking specific aspects in relation to conj, Sext and Trine, together with the negatives as in the Square and the opp, and be able to plot a sinusoidal waveform over time showing the intrinsics of data that would be derived from mountains of figures such as:
    Ear (11) Sxt Mar (1) 9-Jan-07 18°Cn46' D 18°Vi46' D
    Sun (5) Tri Mar (1) 9-Jan-07 18°Cp46' D 18°Vi46' D
    Mer (5) Sqr Mon (9) 9-Jan-07 20°Cp33' D 20°Ar33' D
    in relation to the ever moving geocentric relationships of advancing and retrograde planetary trajectories, as viewed from the far side of the globe, with some knowledge as to topocentric positioning.

    The person should have an interest in extrapolating meaningful heliocentric data from epehmeris tables, Ptolemaic astronomy, and planets on epicycles, or at least a preliminary grasp of Celestial Mechanics and Dynamical Astronomy, perhaps with a little experience in a product such as Alcyone Ephemeris 2.6.

    Would I know the specification of the method to determine the value of delta T (or even when to or to not use?)

    I think this is a task for a secretary, qualified in Excel, VBA, Mathematics, and all things astronomical.


    Good luck in your quest.
    ---

  93. #93
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Bryan

    Of the data sheet I sent you could you get excel to simply show data in a chart. Using two colors red and green. I can manually enter the data, but to plot on a chart is difficult. A simply bar chart that has the date at the bottom and the values run up the side?

    Kindest Regards,
    Martin.

  94. #94
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Martindelica
    Of the data sheet I sent you could you get excel to simply show data in a chart. Using two colors red and green. I can manually enter the data, but to plot on a chart is difficult. A simply bar chart that has the date at the bottom and the values run up the side?

    Kindest Regards,
    Martin.
    Martin,

    the contents of your post #93 seem to indicate that you have either not read posts #87, #90 & #92, or have chosen to totally ignore them and re-ask your question again.

    That's ok - this is a Forum, and you are entitled to post what you want, however when you add my name to your posts you are reducing your chances of receiving a response from someone who has the first clue as to how to produce the results that you are asking for.

    My advice remains.

    Close this somewhat unwieldy thread, which has wandered far from it's original 'Help with Formula' title, there is no formula known to me that will go close to what you are asking.

    Open a new thread stating what sort of 'data' you would like extracted from your page of Alpha-wotnot characters, and the guidance and rules needed to extract the data you are seeking.

    For me, a reading taken on the far side of the globe, at New York, 40°N42'51'', 074°W00'23'' for which you want to plot (say, amongst others) an Earth figure for a range of days when the data shows Ven (5) Sxt Ear (4) 1-Jan-07 27°Cp21' D 27°Sc21' D and Ear (11) Sqr Mon (9) 10-Jan-07 20°Cn33' D 20°Ar33' D will remain a total mystery.

    ---

  95. #95
    Forum Contributor
    Join Date
    10-09-2006
    Posts
    212

    Help with Formula

    Is it possible to have Excel plot this data on a simple bar chart or graph?
    Jan 1 = -7
    Jan 2 = -9
    Jan 3 = 20
    Jan 4 = 16
    Jan 5 = 20
    Jan 6 = -4


    The positive numbers plot a green bar and the negative numbers plot a red bar.

    Martin.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1