A Student wrote:

>  I'm very confused and frustrated about spreadsheet assignment 2.  I can not

>  figure out how to do the formula for the "met and not met" columns.  I have

>  read the lecture and also tried to get help from Excel.  Will you please

>  explain how to do it to me?

>

>  Thank you



There is a hyper link off the assignment page that takes you to an example of how to use 

the IF function. It is at:



http://vu.umkc.edu/CS100/mod.ss/unit.2/ifexamples.html 



That example is very much like the assignment.



I think the first step to doing this problem is to make sure you understand how the answer 

is computed outside of an Excel spreadsheet. For example,



If January sales were $485  and February sales were $493 and the sales target was 3% 

the sales target was not met. Why? The increase from January to February was $8 

($493-$485). Is $8 greater than or equal to 3% of $485? No because 3% of $485 is 

$14.55. So the target was not met.



Now the hard part is how to code an IF function to print Met or Not Met based on the 

two sales figures. In english you want something like:



IF the increase between months is greater than the % increase sales goal

  PRINT "Met"

OTHERWISE

  PRINT "Not Met"



So, the Excel IF statement is going to look something like:



=IF(  ,"Met","Not Met")



Now we need to figure out what that first expression should be.



C4-B4 is the monthly increase (if the sales person had a decrease in sales this number 

could be negative)



B1*B4 is % increase sales goal



So, the first expression to our IF statement should be:



((C4-B4) > (B1*B4))



Note, the expressions above use only relative references. Before

you use the fill down feature to fill out the rest of your

table you will need to make at least one of these references

an absolute reference.



Eddie



A Student wrote:

>

>  I received your response to my question.  I entered the

>  formula=If((c4-b4)>(b1*c4),"Met","Not Met") in column d4.  I also changed the

>  corresponding numbers to met the columns.  The answers did not match the ones

>  that were given.  Could you please tell me where I went wrong.



There are two problems with the formula you have. (One might be of my doing

in an earlier hint I think I have the wrong formula.)



First, it should be (b1*B4) not (b1*c4).



Second,

The formula:



=If((c4-b4)>(b1*b4),"Met","Not Met")



will work for the first line but for the second line and beyond the cell references need to 

be updated. For example, in D5 you should enter:



=If((c5-b5)>(b1*b5),"Met","Not Met")



You could retype each line as you go but a much better way is to use the fill down 

procedure. First notice that only some of the references need to change as you copy the 

formula down. The references that don't change should be made absolute references:



=If((c4-b4)>($b$1*b4),"Met","Not Met")



You can now use the fill down procedure to copy this formula down.



Eddie