Are there any resident Excel wizards?

[Nikodemuzz]

On 04/02/2021 at 14:40, StevieDvd said:

Would a test of the cell content length be suitable?

For example to test:

=IF(LENB(B1)<1, "ignore","use")

The "use" text would have the function to do if the data is there, and the "ignore" would be left blank. You can embed IF statements but it does get messy, looks like you need to break your needs into seperate functions.

Your function might work, thank you! However, if I leave the "ignore" part blank, those cells produce a 0. That results in the COS part of the formula to be zero, but the SIN part will be calculated, which could in some cases give false results. It would be better if those cells were omitted altogether, but I don't know if that can be accomplished.

On 04/02/2021 at 14:56, JSeaman said:

I can't recreate the issue, any chance you could upload the sheet in question?

Sure, although I dread the amount of shame the presentation of my unfinished work will bring...  But hey, open source and all that! I'll have to get back to the computer to do that, should be able to do that in the evening.

On 04/02/2021 at 15:45, Seelive said:

ISBLANK won't work because the cells aren't blank, they contain a formula. Similarly other formula like COUNTIF and COUNTA won't work either, however COUNT and  COUNTBLANK do work. I would change the final "" in the cell IF formula to 0 and then COUNTIF(B3:B23,"<>0") works for me.

Thanks, that explains why I wasn't able to differentiate the cells with those functions. Counting the cells was not the end goal, though. It was to exclude the blank cells from the calculation. I was using the counting functions to study why the logical functions were not working as intended.

[Seelive]

35 minutes ago, Nikodemuzz said:

... I was using the counting functions to study why the logical functions were not working as intended.

I would guess that one of the functions is seeing "" as an invalid number thereby resulting in the error. For example, in cell B1 of a spreadsheet enter IF(A1+1=A1+1,A1,"?"). If cell A1 is truely blank, then B1 will show 0, and for any number entered in A1 cell B1 will be the same number.  But if you set A1 to ="" (or any text character), the result will be #VALUE!

Perhaps ISNUMBER may be a solution as it detects only cells that do actually contain a number and are not text or are not blank.

[StevieDvd]

2 hours ago, Nikodemuzz said:

Your function might work, thank you! However, if I leave the "ignore" part blank, those cells produce a 0. That results in the COS part of the formula to be zero, but the SIN part will be calculated, which could in some cases give false results. It would be better if those cells were omitted altogether, but I don't know if that can be accomplished.

 

The problem could be due to the format of the fields being used in the function and the target cells - are both set to numeric?

In my quick test I have used =IF(LENB(D7)<1,"",D7) which seems to work as I think you needed, but you don't use the double quotes i.e  =IF(LENB(D7)<1,,D7) then I get the #NAME? problem too.

[Nikodemuzz]

3 hours ago, Seelive said:

I would guess that one of the functions is seeing "" as an invalid number thereby resulting in the error. For example, in cell B1 of a spreadsheet enter IF(A1+1=A1+1,A1,"?"). If cell A1 is truely blank, then B1 will show 0, and for any number entered in A1 cell B1 will be the same number.  But if you set A1 to ="" (or any text character), the result will be #VALUE!

Perhaps ISNUMBER may be a solution as it detects only cells that do actually contain a number and are not text or are not blank.

 

2 hours ago, StevieDvd said:

The problem could be due to the format of the fields being used in the function and the target cells - are both set to numeric?

In my quick test I have used =IF(LENB(D7)<1,"",D7) which seems to work as I think you needed, but you don't use the double quotes i.e  =IF(LENB(D7)<1,,D7) then I get the #NAME? problem too.

Thanks for the help! I'll look into these tomorrow, my brain has already punched out for the day.  

[Nikodemuzz]

On 04/02/2021 at 14:56, JSeaman said:

I can't recreate the issue, any chance you could upload the sheet in question?

You can find the file here: https://www.dropbox.com/s/c3smzemb8zsb08u/astrocatalogue.xls?dl=0

The calculation I'm struggling with can be found in the AE column in the Catalogue sheet.

[JSeaman]

The bit it isn't liking appears to be the if statement referencing the array formula, if you take out:

=IF(('Sidereal times'!$B$3:$B$26-Catalogue!K2)<0,('Sidereal times'!$B$3:$B$26-Catalogue!K2)+360,('Sidereal times'!$B$3:$B$26-Catalogue!K2))

And put it in a cell (CTRL+Shift+Enter)

Then reference it with your original formula:

=MAX(DEGREES(ASIN(SIN(RADIANS(P2))*SIN(RADIANS(Variables!B3))+COS(RADIANS(P2))*COS(RADIANS(Variables!B3))*COS(RADIANS(    !!!!Cell location!!!!     )))))

It should work

Edited February 5, 2021 by JSeaman

[Nikodemuzz]

Thank you for the help, this puzzle has now been solved! The calculation works, and referencing a few objects in Stellarium, seems to produce correct results.

The working formula was:

=MAX(IF(ISNUMBER('Sidereal times'!$B$3:$B$26);DEGREES(ASIN(SIN(RADIANS(P2))*SIN(RADIANS(Variables!$B$3))+COS(RADIANS(P2))*COS(RADIANS(Variables!$B$3))*COS(RADIANS('Sidereal times'!$B$3:$B$26-Catalogue!K2))));""))

I realized this IF function was not necessary to begin with: =IF(('Sidereal times'!$B$3:$B$26-Catalogue!K2)<0,('Sidereal times'!$B$3:$B$26-Catalogue!K2)+360,('Sidereal times'!$B$3:$B$26-Catalogue!K2))

It was meant to ensure that the COS has has always a positive value to process, but it makes no difference. So we can leave that out. I wonder why I put it there in the first place.

Now the next (and probably at this point the last) step is to create the monthly target lists!

[JSeaman]

Great, well done and hope it all works well

[Nikodemuzz]

I have a question for you, friends!

Excel has a limitation of array formulas in a worksheet (around 65000). With the way I am approaching this tool, this technical limitation also presents a limit on the amount of objects I can include in the catalogue. With this limitation taken into account, I can have at maximum around 4000 targets included. Granted, this is still quite a bit of targets, but I would like to make sure I don't exclude potentially interesting targets. The question is, how to determine which to leave out?

The NGC and IC catalogues have a combined amount of 13958 targets. Here's how I would approach the problem:

Lets's remove the easy ones first:

- Targets with no reported size, these won't work with the tool anyway (1411 objects)

- Targets that are categorized as duplicates between IC and NGC catalogues (703 objects)

- Targets that are categorized as Star(s), as presumably uninteresting imaging targets (97 objects)

After these we still have around 12 thousand objects left. Here comes a bit of a dilemma. We could just eliminate enough targets, starting from smallest. After all, around 11 thousand of the objects are smaller than M57, for example. On the other hand, especially the larger, more interesting end of the catalogue is quite populated with open clusters (although there are less than a thousand of them), which apart from a few exceptions are not very interesting imaging targets. Perhaps they are underappreciated, but they are not at the top of my list, I can admit. Clearing the open clusters would clear the way for more interesting targets? What do you think?

[Captain Scarlet]

It feels to me to be a good time to start getting to grips with, say, VBA, and “sub-contract” a lot of the processing out of the spreadsheet, particularly large sheet operations like sorting etc.

Macros for the sheet manipulation and Functions for some of the cumbersome calculations.

The learning curve is steep for only a short while then it becomes a joy...

[JSeaman]

Yes agreed, you're getting into stuff that is possible but very difficult without VBA and incredibly easy with it

[Nikodemuzz]

There it is again, the mysterious combination of letters, VBA...

I know you are both right. I can see how I can do the things I'm planning to do without that much effort, once. However, changing things later on will snowball into being a real job real quick. For example, adding or removing telescopes or cameras.

This would probably be a good project to practice on, but let's see if I have enough steam to develop it further when it works. It would be good for me in the long run, too! A bit like fish liver oil as a child! 😃

[JSeaman]

I'll keep an eye out for the 'any resident VBA wizards' thread

[Nikodemuzz]

1 hour ago, JSeaman said:

I'll keep an eye out for the 'any resident VBA wizards' thread

Haha, indeed!

The next task I would like to fulfill is to make monthly calendars, where I have my scope/camera combinations and tiers 1,2,3,4, for example. The idea is to check from the master target list, which of the targets recognized for each scope/camera combination land in which tier in each month (if any tier). To make the table easier I would like to print the results into a single cell instead of a table. Meaning that all targets that are tier 1 for scope/camera 1 in January, would be printed in a single cell. Then tier 2 and so on.

Now I KNOW this would be so much easier to accomplish with VBA. I can already see it won't be as easy as I perhaps thought using just functions as I'm used to. But when it comes to actually doing the bit in VBA, well, you might as well ask me to write a poem in Japanese. I know I can learn to do it, but it will take "some" time.

[JSeaman]

What you are describing is definitely going to be easier in vba, if you do get stuck at all (even getting started) just ping me a message with what you want to do and I'll send you back some code

[Nikodemuzz]

11 hours ago, JSeaman said:

What you are describing is definitely going to be easier in vba, if you do get stuck at all (even getting started) just ping me a message with what you want to do and I'll send you back some code

Much appreciated, thank you! I might well be in need of such assistance sooner than later.