# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Help with structured references

## hellur_kitty

Hi all,

I am attempting an Excel homework assignment, but I've run into some difficulty on this step:

Create a formula with structured references in cell G2 to calculate the percent of the list price (that is, the selling price divided by the list price).

The list price is in E2 (725,000), and the selling price is in F2 (645,750). The formula I came up with (SUM=(Selling_Price/List_Price)) gives me #NAME? in all cells for column G.

Can anyone help me with this step so that I can move on with my assignment?

Thanks so much!!

----------


## FDibbins

Hi and welcome to the forum  :Smilie: 

1st, you dont need to use SUM() for a calc like that  (not that it would cause your formula to not work, its just not necessary)

2nd try replacing the names in your formula with the actual cell references

The only time you can use names like that, is if you have created range names for those cells

----------


## hellur_kitty

Thank you so much, the formula worked just fine!

----------


## hellur_kitty

One last question  :Smilie: 

I am now on this step:

Create a new conditional format rule that applies Yellow fill (fourth column, bottom row) and bold font to values that are greater than or equal to 98% in the Percent of List Price column (G2:G19).

In the new formatting rules dialog box, should I select "format all cells based on their value" or do I need to use a formula to determine which cells to format?

----------


## FDibbins

you would use "format all cells based on their value" is you want to apply the rule to that specfic cell...ie if you want to color G2 AND G2 contains the data that will decide if the change happens

You would use "use a formula" if you want to change the color of G2, but another cell (F2 for instance) contains the  data that will decide if the change happens

Hopefully, based on that, you can decide?

----------


## hellur_kitty

Hmm.. well I think I have to do an IF function, but this all makes my brain want to explode, so I'm not certain. 

If an IF function is what I need to use to apply the conditional formatting only to cells that are greater than or equal to 98%, would this be correct?:

IF=(x>=98%)

----------


## FDibbins

When you create formulas for CF, you need to structure them a little differently.  CF works on a TRUE or FALSE, so your formula needs to give 1 of those as its answer.

What I sometimes do, is practice creating the formula IN the worksheet, next to data that I will be working with.  That way, you can play with the formula until it gives you what you want.

try something like =cell_ref>-0.98

----------


## hellur_kitty

I'm trying the formula =$G$2:$G$19>=.98, but it doesn't register any changes. My professor has literally been of zero assistance, so I'm not sure what mistakes I'm making.

----------


## FDibbins

the cell_ref is a single cell ref, not a range....=$G2>=.98   (note the absolute reference, as well)

when you apply CF, 1st, highight the range you want to apply it to (you can adjust the range inside CF, too)
Then go into CF and create your rule/s.

----------


## Mzgam3

I have the same problem about Creating a formula with structured references to calculate the percentage of list price. The answer above is too vague. Can someone please specify?

----------


## FDibbins

Hi Mzgam3 and welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------

