Selecting two values from a column, for a calculation

Martin Little

I'm a long time lurker here - thanks for all of the other questions and answers that I've learned so much from.

After trying multiple approaches, I've been unable to solve this challenge,, so looking for wise input.

I have the following type of table in Smartsheet (not posting that exact screenshot for confidentiality). Everything works perfectly, apart from the Over/Under column, which should only calculate if the row is a 'TotalAverage', and look at the 'User' column to match the user, then do a subtraction of TotalCap minus TotalAverage. I've tried IF, AND, INDEX, COLLECT, VLOOKUP, but cannot work out how to make this happen. Thank you in advance for any help!

Carson Penticuff

You will have to adjust column names to fit your situation, and I wasn't sure what to do about rows in which the TotalCap and TotalAverage are equal so you may have adjust that as well.

=IF([Type]@row = "TotalAverage", IF(INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalCap"), 1) > INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalAverage"), 1), "Under", IF(INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalCap"), 1) = INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalAverage"), 1), "Equal", "Over")))

Martin Little

Thank you @Carson Penticuff . This was 95% of the approach I took, so good to have that validated.

Here's the actual formula I'm now using. This is throwing an #INVALID VALUE error though. The Lower/Upper column is a dropdown - could that be causing the issue?

=IF([Lower/Upper]@row = "TotalAverage", IF(INDEX(COLLECT([July 23]:[July 23], [Appended name]:[Appended name], [Appended name]@row, [Lower/Upper]:[Lower/Upper], "TotalCapacity"), 1) > INDEX(COLLECT([July 23]:[July 23], [Appended name]:[Appended name], [Appended name]@row, [Lower/Upper]:[Lower/Upper], "TotalAverage"), 1), "Under", IF(INDEX(COLLECT([July 23]:[July 23], [Appended name]:[Appended name], [Appended name]@row, [Lower/Upper]:[Lower/Upper], "TotalCapacity"), 1) < INDEX(COLLECT([July 23]:[July 23], [Appended name]:[Appended name], [Appended name]@row, [Lower/Upper]:[Lower/Upper], "TotalAverage"), 1), "Over")))

Carson Penticuff

I don't believe it will matter if Lower/Upper is a dropdown, unless it is a multiselect dropdown. What about the formatting for the column you are placing this formula into?

Martin Little

Lower/Upper is single select, limited to specific values only. The column this is going into is a plain Text/Number column. I'm truly puzzled on this.

Carson Penticuff

I'm also a little confused. Can you verify that all of your column names match your formula?

Martin Little

I’ve retyped the formula a couple of times now, and it has all gone coloured, which I typically use to confirm column names are in good shape. If I test it in portions, it works until the index collect portion.

Paul Newcome

The #INVALID VALUE error coming from an INDEX/COLLECT is the equivalent of a #NO MATCH coming from an INDEX/MATCH.

What information exactly is housed in the [Lower/Upper] column? Is it the Type column from the above screenshot?

And quick tip to help make things a little easier... Since this is only outputting a value on the "TotalAverage" rows anyway, it can be simplified to remove two of the INDEX/COLLECT pieces all together.

=IF([Type]@row = "TotalAverage", IF(INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalCap"), 1) > July@row, "Under", IF(INDEX(COLLECT([July]:[July], [User]:[User], [User]@row, [Type]:[Type], "TotalCap"), 1) = July@row, "Equal", "Over")))

Martin Little

@Paul Newcome - Lower/Upper is the same as the Type column in my screenshot, yes. So the 'types are repeated once per user.

Thanks for the formula catch - that's updated, but still the same result.

Paul Newcome

Ok. So now we have narrowed it down to either being

INDEX(COLLECT([July 23]:[July 23], [Appended name]:[Appended name], [Appended name]@row, [Lower/Upper]:[Lower/Upper], "TotalCapacity"), 1)

or the data in the July column.

The data is most likely going to be the easiest piece to either rule out or trouble shoot, so lets start there. Exactly how is that column being populated?

Martin Little

Aha - OK. Two updates then:

1. the July 23 column is populated using a fairly complicated formula, pulling from other sheets. There is also another index(collect) in there.
2. I added a new column (Jul23) and only copied the values to it. The formula then works fine against that column, so it must be to do with the formula itself. I tried creating a third column that was simply =[July 23]@row, but that approach didn't work. Any other ideas?

Thanks for your thoughts here.

Paul Newcome

We have two choices. Either we can take a look at the formula populating the July 23 column and see if we can adjust that (it may actually be fairly simple to do), or you can create a helper column and use a VALUE function then reference the helper column in this INDEX/COLLECT.

=VALUE([July 23]@row)