[Year 12 IPM] Nested IFs

Fri May 20 15:06:11 EST 2005

Thank you everyone for all the head aches err help on
excel ;)

I am not the best at excel :( the Vlookup is
different, I don't really understand it, I think I
will have to look at it over the weekend and try to
work it out.

I do like my nested ifs :)

Thanks for the help has certainly given my brain some
food for thought :)

Emma Coughlan
St Helena Secondary College
Eltham
--- "Sheard, Brian B"
<sheard.brian.b at edumail.vic.gov.Au> wrote:

> Hi,
>  
> Re the nested IF's bit. I agree that in lots of
> cases it is simpler to use a vlookup but there are
> cases (e.g. the reporting package we use where there
> are both Alphabetic and Numeric spreadsheets
> according to staff preference) where it makes sense
> to use nested IF's for grades because you can hiode
> the formulae - lock part of the sheet and it is easy
> to pick up any formula change from the one and
> import it into the other. IF (pardon the pun) nested
> IF's are to be taught (and they should be) there are
> a number of good examples out there.
>  
> Brian
> 
> ________________________________
> 
> From: ipm-bounces at edulists.com.au on behalf of Mark
> Kelly
> Sent: Fri 20-May-2005 12:29 PM
> To: Year 12 Information Technology Processing and
> Management Teachers'Mailing List
> Subject: [Year 12 IPM] Nested IFs
> 
> 
> 
> The nested IF issue grabbed my curiosity.  Went
> Googling to try and find
> a really good case of where a nested IF was
> essential.
> 
> My first Googling found...
> http://www.cpearson.com/excel/nested.htm
> 
> It presents the case of...
> 
> > Suppose we wanted an nested IF formula to test:
> > IF A4 = 1 Then 11
> > Else If   A4 = 2 Then 22
> > Else If   A4 = 3 Then 33
> > Else If   A4 = 4 Then 44
> > ...
> > Else If   A4 = 13 Then 130  Else "Not Found"
> 
> The author then creates a massively complicated IF
> solution.
> 
> This is an example of where you just would not use
> nested IF at all.
> You would use VLOOKUP.  Since the lookup values are
> simply 1 to 13, just
> use:
> 
> IF A4=  RETURN VALUE
> 1       11
> 2       22
> 3       33
> 4       44
> ..      ..
> 13      Not found
> 
> (I hope the tabs above survive MIME encoding and the
> numbers don't crash
> into each other)
> 
> Name the above range "table" and use
> =VLOOKUP(A4,table,2)
> 
> Far better than twisting yourself up with IF...
> 
>
-----------------------------------------------------------------------------
> 
> Hmmm, just found the CHOOSE() function which lets
> you easily look up
> values for an index value e.g.
> 
> =CHOOSE(daynumber,"Sunday","Monday","Tuesday"...
> etc)
> 
> That may be useful too!
> 
>
-----------------------------------------------------------------------------
> 
>
---------------------------------------------------------------------------
> 
> Second Google led to...
>
http://spreadsheets.about.com/cs/excelfunction1/a/nestediffunct.htm
> 
> It says...
> 
> > To illustrate, let's say you have a spreadsheet
> that you use to keep
> > track of your sales force. The rate of commission
> each sales person
> > receives is based on the amount of sales they have
> generated for that
> > month. For example:
> 
> > From $1 to $10 earns 10% commission
> > From $11 to $100 earns 15% commission
> > Anything over $100 earns 20% commission
> 
> > Assuming the amount of sales is in column B,
> starting at row 4, and
> > that the column containing the commission is
> formated for percentages,
> >  this is what the nested IF function would look
> like:
> 
> > =IF(B4<=10,"10", if(b4<=100, "15", "20"))
> 
> Once again, a simple VLOOKUP is far superior.
> 
> FROM   TO    COMMISSION
> 1      10    10%
> 11     100   15%
> 101          20%
> 
> Name the range (excluding headings) "table" and then
> use
> =VLOOKUP(sales,table,3)
> 
>
-------------------------------------------------------------------
> 
> Note the method I now use for setting out lookup
> tables.  I used to get
> mental cramps working out the cutoff values for
> categories.  Now I use
> "From" and "To" columns to make it much easier to
> determine the numbers.
>   The second column is actually never used by the
> lookup, but it helps
> my brain.  Life is som much more pleasant now  :-)
> 
> 
>
---------------------------------------------------------------------
> 
> Example 3, found at
>
http://www.techonthenet.com/excel/formulas/if_nested.htm
> 
> Question:  In Excel, I need to write a formula that
> works this way:
> 
>  > If (cell A1) is less than 20, then times it by 1,
>  > If it is greater than or equal to 20 but less
> than 50, then times it by 2
>  > If its is greater than or equal to 50 and less
> than 100, then times
> it by 3
>  > And if it is great or equal to than 100, then
> times it by 4
> 
>  > Answer:  You can write a nested IF statement to
> handle this.  For
> example:
> 
>  >  =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100,
> A1*3, A1*4)))
> 
> 
> Once again, it's far easier to use
> 
>  From    To    Multiplier
> 0       19    1
> 20      49    2
> 50      99    3
> 100           4
> 
> Name the range "table" (or whatever)
> 
> In the target cell just put =a1 *
> VLOOKUP(A1,table,3)
> 
>
---------------------------------------------------------------
> Example 4, found at
>
http://www.techonthenet.com/excel/macros/if_custom.htm
> 
>  > Question:  I have a formula in Excel that I am
> using to test for 7
>  > conditions, and each condition if true will
> return a different value.
>  > However, I now need to test a total of 12
> possible values.  The
>  > limitation of the nested IFs is that you can only
> nest up to 7.  Is
>  > there an alternative to this formula to test so
> that I can test for 12
>  > values instead of 7?
> 
>  >
>
=IF(A1="10X12",120,IF(A1="8x8",64,IF(A1="6x6",36,IF(A1="8x10",80,IF(A1="14x16",224,IF(A1="9x9",81,IF(A1="4x3",12)))))))
> 
>  > Answer:  There is no built-in alternative formula
> in Excel, but you
>  > could write your own function in VBA and then
> call this new function
>  > instead.
> 
> Or you could (again) use vlookup...
> 
> 
=== message truncated ===

Enjoy your day as you never know whats around the corrner

___________________________________________________________ 
How much free photo storage do you get? Store your holiday 
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com