Are there any resident Excel wizards?

[Nikodemuzz]

I'm not sure if this is the correct place to ask this, but I hope I might get some assistance with Excel. I'm building a tool for astrophotography planning. I'm planning to share it with the community, provided I manage to finish it! 😃

The Excel problem I'm having is the following:

I'm compiling a list of data in Sheet3. In order to do that, I'm comparing values in a table (Sheet 2) to a dataset (Sheet 1). I check if the values in a certain column in Sheet 1 match the criteria set in Sheet 2, and if so, return a value from another column in Sheet 1. This works well as long as I have only one criteria to check. The problem arises when I need to check if the reference number is between input values. In other words, check if the value is in a set range.

I have discovered that the AND function does not work with arrays. If I input my formula as "not an array", it works fine. I have tried to get around the AND function by multiplying the conditions, but get the same result: works as a normal formula, not as an array.

I am at a loss as to how to proceed. Could anyone offer some advice? 

Example of the formula using AND:

=IF(AND((Sheet1!V2>Sheet2!$D$5);(Sheet1!V2<Sheet2!$D$4));"TRUE";"FALSE")

Example of the formula not using AND:

=IF((Sheet1!V2>Sheet2!$D$5)*(Sheet1!V2<Sheet2!$D$4);"TRUE";"FALSE")

Example of an array formula not using AND, that doesn't work: 

{=IF((Sheet1$V$2:$V$12539>Sheet2!$D$5)*(Sheet1!$V$2:$V$12539<Sheet2!$D$4);"TRUE";"FALSE")}

The previous examples describe the logical test, which is the problem area in my formula. The complete formula is larger. Example of the complete formula that works (only one criteria):

=IF(ISERROR(INDEX(Sheet1!$A$2:$A$12539;SMALL(IF(Sheet1!$V$2:$V$12539>Sheet2!$D$4;ROW(Sheet1!$V$2:$V$12539));ROW(Sheet1!1:1))-1;1));"";INDEX(Sheet1!$A$2:$A$12539;SMALL(IF(Sheet1!$V$2:$V$12539>Sheet2!$D$4;ROW(Sheet1!$V$2:$V$12539));ROW(Sheet1!1:1))-1;1))

Sorry for the long post!

[jackp93]

Hi 

 

if I’m understanding correctly you may want to try vlookup as this will then compare the two sets of data and then you can specify which data it will then return.

 

hope this helps

 

all the best

 

Jack

[BCN_Sean]

It's been a good few years since I've used Excel and longer since I've used it in English, so maybe things are out of whack; but why not try using the min or max function on the array/range and running the greater / less than comparators against that, so something like this:

=IF(AND((MAX(Sheet1!V2)>Sheet2!$D$5);(MIN(Sheet1!V2)<Sheet2!$D$4));"TRUE";"FALSE");

[Seelive]

The only problem with vlookup (or hlookup) is that it will only find an exact match (final field set to FALSE) or the nearest value (final field set to TRUE) but the lookup table must be in ascending order otherwise you will get some unexpected results. Have you considered using Excel VBA? 

Edited January 31, 2021 by Seelive
Typo

[JSeaman]

It would be easier with the sheet if you can attach it?

Maybe a simpler way of solving it would be to put your TRUE/FALSE as a column in sheet 1, it would be easy to have an if statement referencing Sheet2!$D$4 and Sheet2!$D$5 which gives your true/false and then a vlookup would be simple

VBA is also an easy way to solve this

[Spile]

I use this forum for ideas and specialist spreadsheet knowledge...

https://www.mrexcel.com/board/

[Nikodemuzz]

3 hours ago, jackp93 said:

Hi 

 

if I’m understanding correctly you may want to try vlookup as this will then compare the two sets of data and then you can specify which data it will then return.

 

hope this helps

 

all the best

 

Jack

Thanks for the suggestion! If I understand correctly vlookup has restrictions in this case. I'll explain further in a subsequent message. 

[Nikodemuzz]

3 hours ago, BCN_Sean said:

It's been a good few years since I've used Excel and longer since I've used it in English, so maybe things are out of whack; but why not try using the min or max function on the array/range and running the greater / less than comparators against that, so something like this:

=IF(AND((MAX(Sheet1!V2)>Sheet2!$D$5);(MIN(Sheet1!V2)<Sheet2!$D$4));"TRUE";"FALSE");

This might work, although the problem is not with the comparisons, it is with the AND function which I would still have to use in this case.

[Nikodemuzz]

3 hours ago, Seelive said:

The only problem with vlookup (or hlookup) is that it will only find an exact match (final field set to FALSE) or the nearest value (final field set to FALSE) but the lookup table must be in ascending order otherwise you will get some unexpected results. Have you considered using Excel VBA? 

This is exactly the problem with vlookup in my case. I'm not proficient with VBA, so I have been trying to avoid it. Could well be that I have arrived to the point where I no longer can.

[Nikodemuzz]

3 hours ago, JSeaman said:

It would be easier with the sheet if you can attach it?

Maybe a simpler way of solving it would be to put your TRUE/FALSE as a column in sheet 1, it would be easy to have an if statement referencing Sheet2!$D$4 and Sheet2!$D$5 which gives your true/false and then a vlookup would be simple

VBA is also an easy way to solve this

Your suggestion of putting the TRUE/FALSE in a separate column could actually be the perfect workaround to the problem, thank you!

To help everyone understand a bit better what I'm trying to do, I'll explain the background a bit more.

In "Sheet1", I have the NGC and IC catalogues of objects with their positional and apparent size data. In "Sheet2" I have calculated the fields of view I get with the desired combination of telescopes and cameras. Let's say these are FOV1, FOV2 and FOV3, with FOV1 being the largest. In "Sheet3" I'm compiling a list of objects that are suitable for each telescope/camera combination. The formula I gave above as an example compares the apparent sizes of the objects to FOV2, and if it is larger, adds the object name to the list.

The ultimate objective of this excercise is to have tool that shows you what objects are the best to shoot with your available gear, in your location, divided monthly. 

[Seelive]

26 minutes ago, Nikodemuzz said:

This is exactly the problem with vlookup in my case. I'm not proficient with VBA, so I have been trying to avoid it. Could well be that I have arrived to the point where I no longer can.

I first started to use it only a few years ago when I had a problem that I couldn't find an elegant solution just using Excel formula by themselves. I felt the same but there is so much info on the web that it doesn't take long to pick up the basics and I've never looked back. These days I use it for just about everything, including processing my astro images.

[Captain Scarlet]

In such situations I always use the MMULT function, which stands for Matrix Multiply, and follows the strict rules of matrix multiplication. Combine that with the fact that you can also use <, >, =, >=, and <> as actual operators like + - / * , and you have a very powerful tool.

Such as

MMULT(TRANSPOSE(AND($A$2:$A$20>$A$1,$A$2:$A$20<=$B$1)*1),$B$2:$B$20)

... where A2:A20 contain, say, dates, B2:B20 contain values and A1 and B1 contain the min and max dates you want to use to filter that list.

TRANSPOSE is there because it’s strict matrix mult rules so you need 1x19 and 19x1 to be able to multiply.

the mysterious “*1” is there because (1>2) evaluates within a formula to FALSE, and the *1 forces that to become 0

Edited January 31, 2021 by Captain Magenta
More detail

[JSeaman]

That's a nice idea for a sheet

I tend to find when I'm entering a formula like you had in the original post that I can boil it down to a couple of simple (and usually hidden) columns so hopefully that workaround will do the trick

VBA is easy if you're familiar with coding, it's slightly daunting if you aren't. It is definitely worth a look though if you're getting to the advanced use of Excel you are approaching

A blind example of some VBA code that could get you started if you go down that route:

Option Explicit
Const OBJECT_NAME_COLUMN As Integer = 3
Const OBJECT_SIZE_COLUMN As Integer = 3

Const FOV1_ROW As Integer = 1
Const FOV1_COL As Integer = 3
Private Sub CommandButton1_Click()
Dim RowCounter As Integer
Dim ObjectSize As Double

'Start from row 1 on sheet 1
RowCounter = 1

Do
    'As long as the target info is present (this should only happen if the first row was empty)
    If Sheet1.Cells(RowCounter, OBJECT_NAME_COLUMN) <> "" Then
        'Extract the size of the object
        Size = Sheet1.Cells(RowCounter, OBJECT_SIZE_COLUMN)
        
        If (Size < Sheet2.Cells(FOV1_ROW, FOV2_COL)) Then
            'Yay it works for this FOV
        End If
        
    End If
    
    'Move to the next row
    RowCounter = RowCounter + 1
'Keep going until we run out of data
Loop Until (Sheet1.Cells(RowCounter, OBJECT_NAME_COLUMN) = "")
End Sub

 

[JamesF]

I have nothing to add, but now you've made me feel the need to post this.  It's even relevant to astronomy, not least because Matt Parker is married to Lucie Green, the solar scientist and occasional presenter on The Sky at Night.

James

[Captain Scarlet]

1 hour ago, JamesF said:

I have nothing to add, but now you've made me feel the need to post this.  It's even relevant to astronomy, not least because Matt Parker is married to Lucie Green, the solar scientist and occasional presenter on The Sky at Night.

James

Very good 

[JamesF]

17 minutes ago, Captain Magenta said:

Very good 

I think their shows are a lot of fun and very entertaining whilst encouraging people to think a bit more about science.  The off-the-cuff comment in the video above about the phone "smelling slightly of pickle" is a reference to an earlier segment of the show where they demonstrate how sodium street lamps work by electrifying a pickle, for example :)  Over the last three or four years I've bought all three of the "Festival of the Spoken Nerd" DVDs for my children and they've really enjoyed them.  I'm not sure I've seen much of Helen Arney outside the group, but Matt Parker and Steve Mould also do a lot of "science communication" work that's also available on youtube.  Definitely recommended.

James

[Nikodemuzz]

23 hours ago, JamesF said:

I have nothing to add, but now you've made me feel the need to post this.  It's even relevant to astronomy, not least because Matt Parker is married to Lucie Green, the solar scientist and occasional presenter on The Sky at Night.

James

Haha, touche!

[Nikodemuzz]

On 31/01/2021 at 17:24, JSeaman said:

Maybe a simpler way of solving it would be to put your TRUE/FALSE as a column in sheet 1, it would be easy to have an if statement referencing Sheet2!$D$4 and Sheet2!$D$5 which gives your true/false and then a vlookup would be simple

This was the solution, thank you very much!

[Nikodemuzz]

On 31/01/2021 at 22:13, JSeaman said:

That's a nice idea for a sheet

I tend to find when I'm entering a formula like you had in the original post that I can boil it down to a couple of simple (and usually hidden) columns so hopefully that workaround will do the trick

VBA is easy if you're familiar with coding, it's slightly daunting if you aren't. It is definitely worth a look though if you're getting to the advanced use of Excel you are approaching

A blind example of some VBA code that could get you started if you go down that route:

Option Explicit
Const OBJECT_NAME_COLUMN As Integer = 3
Const OBJECT_SIZE_COLUMN As Integer = 3

Const FOV1_ROW As Integer = 1
Const FOV1_COL As Integer = 3
Private Sub CommandButton1_Click()
Dim RowCounter As Integer
Dim ObjectSize As Double

'Start from row 1 on sheet 1
RowCounter = 1

Do
    'As long as the target info is present (this should only happen if the first row was empty)
    If Sheet1.Cells(RowCounter, OBJECT_NAME_COLUMN) <> "" Then
        'Extract the size of the object
        Size = Sheet1.Cells(RowCounter, OBJECT_SIZE_COLUMN)
        
        If (Size < Sheet2.Cells(FOV1_ROW, FOV2_COL)) Then
            'Yay it works for this FOV
        End If
        
    End If
    
    'Move to the next row
    RowCounter = RowCounter + 1
'Keep going until we run out of data
Loop Until (Sheet1.Cells(RowCounter, OBJECT_NAME_COLUMN) = "")
End Sub

 

Thanks for taking the time to write out an example, kudos! I can follow along your code and understand reasonably well what is happening. Applying it myself is another matter, daunting being a rather good word to describe the thought.

Edit: typo

Edited February 1, 2021 by Nikodemuzz

[Nikodemuzz]

I was hoping that I would have had my ducks in a row to complete my mighty spreadsheet, now that the aforementioned problem was solved. Alas, it wasn't meant to be. It seems I have run into another conundrum. Who knew!  Hopefully you can again lend a hand!

At the moment I am building the calculation that computes the elevation of the targets in the sky at chosen moments. As I am building a yearly calendar, I have simplified things by choosing only one day from each month to study. For these dates (always the 15th) I'm calculating the hourly elevation of the targets. As I'm only interested in the points of time when it is dark enough to observe, I have also calculated the local hourly elevation of the sun. Then, the local times must be converted to standard time, or sidereal time. As a result I get a table of sidereal times for the moments when it is dark, like so:

As a next step I want to solve the monthly highest elevation for each target. My thinking was that using the above table I could handily exclude the times when it isn't dark. Following this step I could categorize the targets into tiers based on their monthly highest elevation. In order to find the monthly maximum elevation I have tried to use the following formula:

{=MAX(DEGREES(ASIN(SIN(RADIANS(P2))*SIN(RADIANS(Variables!B3))+COS(RADIANS(P2))*COS(RADIANS(Variables!B3))*COS(RADIANS(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)))))))}

Inside the MAX function is the elevation calculation that uses the observation latitude (Variables!B3), target coordinates in RA (K2) and DEC (P2), and the sidereal time. The idea is to use MAX to find the sidereal time that produces the highest elevation. The MAX function normally excludes blanks, but in this case I get a #VALUE error from my formula. If I limit the range to only the non-blank cells (ie. B1:B10), it works.  Now, if I examine the blank cells with COUNTIF, IF, ISBLANK etc to see how Excel perceives the blank cells, it in fact does not consider them as blank (ISBLANK returns FALSE). Similarly, if I tell it to output 0 instead of blank into the cells when it is not dark, I find some strange behaviour. Excel can correctly count 9 zeros from cells B9:B17, but if I ask it to count non-zero cells ("<>0") from B3:B26, I get 24. At least to me, this makes it hard to exclude them from the calculation with logical functions.

Could anyone offer some advice as to how can I get the calculation working, while fulfilling the target of not taking into account the times when it isn't dark enough to do observations?

[Captain Scarlet]

You can force Boolean results, arrays or cells, to be numeric by either adding 0 or multiplying by 1, so (FALSE)+0 should evaluate to 0

[Nikodemuzz]

2 minutes ago, Captain Magenta said:

You can force Boolean results, arrays or cells, to be numeric by either adding 0 or multiplying by 1, so (FALSE)+0 should evaluate to 0

Thanks Magnus! This is true, however, here my problem is that I'm getting wrong Boolean results. Excel gives me a list of all FALSE to an ISBLANK query, while there are blank cells present. Or counts 24 non-zero cells, even though there are zeros present. 

[StevieDvd]

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.

[JSeaman]

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

[Seelive]

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.