CODEWITHSUNDEEP
excelexcel-formulaformula
user4681670
user4681670

Reputation: 135

How this Formula Works?

I'm looking at a legacy spreadsheet and I cannot for the life of me figure out how this formula works. I'm by no means an excel wizard.

The spreadsheet tracks billing for a company for members of staff. On each row, the user puts an ID, the amount, and formula then puts the figure under a column for a given staff member.

Screenshot of sample spreadsheet

And the formula:

=IF(A2="Foo",A2="Foo")*(SUM(B2))

I would have done this:

=IF(A2="Foo", B2, 0)

Which appears to have the same effect.

Any insight?

Upvotes: 1

Views: 65

Answers (2)

Forward Ed
Forward Ed

Reputation: 9894

The original formula has a lot of excess calculations going on in it. It can be simplified as you mentioned, which is what most people would do. Some alternate forms might be:

=(A2="Foo")*B2

The above formula you would place in D2 and copy down and then you would have to redo the String comparisson for column E then again for F. If you used the following formula you could place it in D2 and copy it down and to the right as needed without having to adjust the formula.

=($A2=D$1)*$B2

In more common terms it would look like

=IF($A2=D$1,$B2,0)

Upvotes: 1

jsheeran
jsheeran

Reputation: 3037

=IF(A2="Foo",A2="Foo") is equivalent to =A2="Foo". It results in a boolean value, which is used by the multiplication operation as 1 or 0.

I don't see any reason to express it that way instead of the more concise and intuitive formula that you offer.

Upvotes: 2

Related Questions