# Selecting two values from a column, for a calculation

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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")))

• Options

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")))

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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")))

• Options

@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.

• ✭✭✭✭✭✭
Options

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?

• Options

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?