Excel spreadsheet

  • Thread starter Thread starter jslb783
  • Start date Start date
J

jslb783

I am beginning an Excel class and I can not figure this out. Could
someone please help me figure out how the formula for this is suppose
to look?

If the content of cell C4 is Bronze, then insert the word None. If
the content of cell C4 is Silver then insert $3,000. If the content
of cell C4 is Gold, then insert $5,000. If the content of cell C4 is
Platinum, then insert $10,000.

I would be very much appreciatvie.

Thanks
 
I am beginning an Excel class and I can not figure this out.  Could
someone please help me figure out how the formula for this is suppose
to look?

If the content of cell C4 is Bronze, then insert the word None.  If
the content of cell C4 is Silver then insert $3,000.  If the content
of cell C4 is Gold, then insert $5,000.  If the content of cell C4 is
Platinum, then insert $10,000.

I would be very much appreciatvie.

Thanks
Click to expand...

One way...

=VLOOKUP(C4,{"Bronze","None";"Silver",3000;"Gold",5000;"Platinum",
10000},2,FALSE)

Apply Currency format with 0 decimal places to the cell with the
formula.

Ken Johnson
 
The question is phrased like you need to use the If() statement.

BACKGROUND INFORMATION
The syntax is:

IF(logical_test,value_if_true,value_if_false)

Whilst the syntax says value_if_..., you can insert another formula that
returns a value. So, you can nest If statements within If statments (up to
seven). For your needs, you need If() to return a value if true or test
again if value_is_false. So, your sytax becomes:

IF(logical_test,value_if_true,IF(logical_test,IF(logical_test,value_if_true,IF(logical_test,value_if_true,IF(logical_test,value_if_true,value_if_false)))))

(All I did to write the above was copy the original syntax, highlight
value_if_false four times, pasting each time).


THE ANSWER

So, your formula becomes:

Either

=IF(C4="Bronze","None",IF(C4="Silver",3000,IF(C4="Gold",5000,IF(C4="Platinum",10000))))

(note above words enclosed in double quotes ("") are interpreted by Excel as
strings (words) whilst other values (3000, 5000 are 10000) are interpreted
as numbers.
To finish the job, format the cell as currency with 0 decimal places.

OR

=IF(C4="Bronze","None",IF(C4="Silver","$3,000",IF(C4="Gold","$5,000",IF(C4="Platinum","$10,000"))))

All values returned above are strings.


FURTHER INFORMATION

To avoid using nested ifs:

http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions/
 
For detailed guidance, please look up the help documentation in Excel for
the IF(), VLOOLUP() and HLOOKUP() functions.
 
One way...

=VLOOKUP(C4,{"Bronze","None";"Silver",3000;"Gold",5000;"Platinum",
10000},2,FALSE)
Click to expand...

I think that's probably more advanced than someone just starting on
excel would use! :p
 
Yeah, I would say this solution is probably what the
class is looking for. Using LOOKUP functions is a
little advanced at this stage. I tried this and it worked
just great!
 
Four levels of nested If() seems unnecessary for learning. However, we don't
know whether Paul is at the beginning of an intro, intermediate or advanced
course. Nor whether it is to test 'how would you solve this'. One good
thing, he'll get a good range of choices here!
 
Back
Top