Help needed : Ideas for getting calculations in reverse

[chaitreya]

The sample excel enclosed contains formulas for calculation of Ascendant at 9:00 AM current date at a specific time zone / latitude / longitude.

However, I need to calculate in just reverse way. I mean at what time, the ascendant will be 0 degree / 30 degree / 60 degree...etc. (for same date)

Can someone help please ?

Thanking in advance.

[MrShorty]

A few generic ideas:

1) Usually the first suggestion is to review the equation(s) of interest and see if there is an algebraic/geometric means of deriving the formula for the inverse function. In most cases, this is the easiest way to perform inverse calculations.

2) Since I am not familiar with the concept of "ascendant", I looked it up: http://en.wikipedia.org/wiki/Ascendant Assuming this is the correct concept, I do not readily see a way to solve for A at a given ascendant. In cases like this, where it is very difficult or impossible to find an expression for the inverse function, one must then resort to numeric "root finding" algorithms: http://en.wikipedia.org/wiki/Root-finding_algorithm

2a) By far the "easiest" way to implement a numeric root finding algorithm in Excel is to use the built in Goal Seek or Solver utilities (which use a Newton-Raphson type method). I would add a cell where you can enter the desired ascendant value, then add another cell that takes the calculated ascendant value-desired ascendant value. Then tell Goal Seek/Solver to "set the cell" with this difference "to a value of" 0 "by changing" the input time cell. Then, double check that the algorithm converged on the correct value.

The main downside here is that it appears that you have several values you want to solve for. As tedious as it may be, it may be easiest to run solver then copy/paste the values into your table, then repeat the process at different values of ascendant. Even though this "manual" approach is tedious, I often find that it is easier than going to the effort of developing and debugging a more automated solution.

2b) If one is familiar enough with developing a root finding algorithm, these algorithms can be coded into a spreadsheet or VBA (depending on which programming language you prefer to use). They are not necessarily difficult to program in. As I noted above, though, for someone who is unfamiliar with these algorithms or how to code a routine that invokes Goal Seek/Solver multiple times, it can be more time and effort than would be expended doing it manually.

At this point, I would suggest solution 2a, unless you feel that you have sufficient programming skills to tackle the problem using 2b.

[chaitreya]

I had tried for a solution for this issue.

The enclosed sample excel contains Ascendant values every minute using same formula.(in Data sheet)

However, in Result sheet I have 2 problems still needing some help from experts.

1. With LOOKUP function used in Result! C5 & C11, it stuck at the end of range..i. e. at 360...I wish...it should consider AG3-AH3 range again after AG14-AH14...

2. With VLOOKUP function in C6 & C12, it should show the 'Time' where the nearest value in DATA!V matches with Result!C5 , C11

Can someone suggest the correct formulas please ?

[MrShorty]

One of the problems you have is that you have set the 4th argument in the VLOOKUP() function to FALSE -- which means that Excel will search for an exact match. (review help file with description of arguments: https://support.office.com/en-us/art...rs=en-US&ad=US )

If this were my spreadsheet, I would sort the lookup table in ascending order by column V, then set the 4th argument to the VLOOKUP function to TRUE.

Is that something you can work with?

[chaitreya]

I have tried on this suggestion, but couldn't get the desired results.

[MrShorty]

Do you have an example of what you have tried along these lines? Did it give the wrong result? an error? What result did you get and what result did you expect?

[chaitreya]

Sample with 4th argument to the VLOOKUP function 'TRUE'.

[MrShorty]

You neglected to sort the lookup table on column V. Currently, column V starts at 270, increases to 360, then goes back to 0 and increases again. As noted in the help file, to get correct results, column V must be sorted in ascending order when the 4th argument is set to TRUE.

Try sorting the lookup table on column V and see if that helps.

[chaitreya]

Since the values are 'planetary' degrees,it was not possible to sort in ascending order.

However, I had converted those values in 'Text'.

The sample excel enclosed contains a set of texts (in 'A') repeating in cyclic order and 'E' represents the corresponding 'Time'.

Now, the expected results are, to search the 'Time' for the LAST entry of the 'text value'.

Can someone help to suggest the formula ?

[MrShorty]

I hesitate to respond, because my first thought is to rearrange the data again. As documented in the VLOOKUP() help file above, when lookup-value occurs multiple times in the lookup table (and the 4th argument is FALSE), Excel returns the result from the first instance of lookup-value in the lookup table. There is not a lookup function that automatically finds the last instance of lookup-value. Therefore, I would first look for an approach that will move what is now the "last" instance to be the first instance. As currently arranged, the time column appears to be in ascending order, and you want to find the last instance of each text string. It seems to me that this is equivalent to sorting on the time column in descending order, then finding the first instance of lookup-value -- which should be almost automatic with a simple VLOOKUP() formula with the 4th argument set to FALSE. Is that something you can work with?

[chaitreya]

Thanks for suggestions.

However, I had managed it somehow by looking for next value with same formula and then using (-1) to get desired result.

In earlier matter of 360 degree issue, used 'IF' function like

=IF(N3=360,E3,LOOKUP(N3,SS!$D$3:$D$245,SS!$E$3:$E$245))

[chaitreya]

Thanks for suggestions.

However, I had managed it somehow by looking for next value with same formula and then using (-1) to get desired result.

In earlier matter of 360 degree issue, used 'IF' function like

=IF(N3=360,E3,LOOKUP(N3,SS!$D$3:$D$245,SS!$E$3:$E$245))