IF function and Structured references need syntax help

New Contributor

I have spent hours trying to figure this formula out, and I am so tired and ready for this to end. I need help trying to set up syntax. To enter a formula using the IF function and structured references that test whether the value in the "tier" field is equal to "platinum," if it is, multiply the value in the "total sales" field by 0.05. Otherwise, enter 0 in the cell. 

 

Please please, pretty please help

6 Replies

@MITVal23 

 

It would help give you a specific formula if you were to show us the actual layout of your data. Absent that, only a general idea of the IF formula can be supplied.

 

So, assuming "tier" is in cell C2 and "total sales" is D2, then this formula could go in E2 (or wherever you want it to be). 

=IF(C2="platinum",D2*.05,0)

Thank you so much!@mathetes 

It worked, and I am thankful because I was ready to pull my hair out. LOL

@mathetes 

Where did the structured references go?  Why not

= IF([@tier]="platinum", [@sales]*5%, 0)

But then, I am the only person on this forum that refers to the A1 notation as an abomination that has no place in any computational environment!

@Peter Bartholomew 

Where did the structured references go?  Why not ...

But then, I am the only person on this forum that refers to the A1 notation as an abomination that has no place in any computational environment!

 

Well, Peter, had there been a table in evidence, with those headings, that's how it would have come out, but you'll have to admit we were also dealing with a very basic question, and the A1 notation is still how we all begin.

@Peter Bartholomew 

 

Come to think of it, we also advocate against hard-coding values in a formula. So why wouldn't

= IF([@tier]="platinum", [@sales]*5%, 0)

 become something like

= IF([@tier]="platinum", [@sales]*PctMult, 0)

  referring to a named range off to the side?

@mathetes 

"The A1 notation is still how we all begin"

 

It is, because that is what is discoverable by trial and error and that is what the 'tips and tricks' genre teaches.  Every now and then, I contemplate how one might go about teaching Excel subject to the rule 'never use direct cell references'.  

 

The speculation was triggered by the FAST financial modelling standard that includes 'do not use defined names' and 'do not use array formulas', both of which I objected to as statements of good practice.  To migrate from 'standard practice', I wrote event handlers to identify formula cells that contained direct cell references (a RegExp routine helped) and convert any such cells to text format with red text on a yellow fill.

 

The initial part of such an alternative course would be, first use a Table or a Defined Name to declare a region of the worksheet as part of the solution space and to link the data to the problem domain (any data lying outside such a region cannot be referenced and, therefore, are mere annotation).

 

Something that the direct notations do well is create relative references such as

= R[-1]C + RC5

(though A1 notation is less effective at showing the intent).

That said, when one examines the purpose of such referencing, it is usually to replicate the functionality of an array formula element by element.  One array formula will usually do the job better and is less prone to errors (such as the Reinhart-Rogof error that plunged much of the world into austerity).

 

A remark by Dan Bricklin during his TED talk on the birth of VisiCalc was: "it [cell referencing] could be done the programmer's way using variables, but that would be tedious!"  The plus of that observation is that it probably paved the way to the incredible world-wide success of the electronic spreadsheet; the minus is that barely 10% of those spreadsheets do not contain significant errors.

 

'Normal' spreadsheet practice is so engrained within 'muscle memory' that I cannot see it changing anytime soon, which leaves me heading off in a different direction and makes answering forum questions somewhat tricky.