[mso] FW: Nestled If's in Excel

Content-Type: text/plain;
        charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
=20

Hi Guys,
=20
I am having some problems with microsoft excel, in that I have been
trying to create some lookup's with nestled if functions (until I
realised that there can't be more than 8 within one cell!).
=20
Here is the situation:
=20
As a communications broker, we sell various products available on the
market.
One of these products is an ASC Landline package.
The package works as follows:
=20
There are Tiers, which change depending upon how much the customer
spends. For the purpose of the spreadsheet I am wanting to compare
against 3 tiers.
=20
Within these tiers, there are also 4 different options (Option 1, Option
2, Option 3, Option 4).
=20
I want to show a savings analysis for the rates we can offer, and
therefore show a saving across the account.
=20
I have set up two drop down boxes, one with Tiers, and one with Options.
=20
The idea is to have the rates they would pay (and therefore the
percentage savings) automatically inserted into the chart depending on
what options are selected from the drop down boxes.
=20
The equation I was going to use, before I realised about the limit is as
follows:
=20
=3DIF((AND(G3=3D"ASC Business Advantage Tier 1",G4=3D"Option 1")),D51,
 IF((AND(G3=3D"ASC Business Advantage Tier 1",G4=3D"Option 2")),E51,
 IF((AND(G3=3D"ASC Business Advantage Tier 1",G4=3D"Option 3")),F51,
 IF((AND(G3=3D"ASC Business Advantage Tier 1",G4=3D"Option 4")),G51,
 IF((AND(G3=3D"ASC Business Advantage Tier 2",G4=3D"Option 1")),D74,
 IF((AND(G3=3D"ASC Business Advantage Tier 2",G4=3D"Option 2")),E74,
 IF((AND(G3=3D"ASC Business Advantage Tier 2",G4=3D"Option 3")),F74,
 IF((AND(G3=3D"ASC Business Advantage Tier 2",G4=3D"Option 4")),G74,
 IF((AND(G3=3D"ASC Business Advantage Tier 3",G4=3D"Option 1")),D97,
 IF((AND(G3=3D"ASC Business Advantage Tier 3",G4=3D"Option 2")),E97,
 IF((AND(G3=3D"ASC Business Advantage Tier 3",G4=3D"Option 3")),F97,
 IF((AND(G3=3D"ASC Business Advantage Tier 3",G4=3D"Option 4")),G97,0))
=20
This equation would fill one box (in this case the local calls).
=20
=20
=20
=20
=20
=20
The data the boxes needs to look at is as follows (bear in mind there is
also a tier 3 table below:
=20
=20
=20
Any suggestions would be much appreciated.
=20
Thanks,
=20
=20

Edward Wenman

  Corporate Accounts Manager

  Mob:   0416 429 738

  Ph:     07 3872 3000

  Fax:   07 3872 3099

  =20

  =20

=20

=20

Scanned by the NetBox from NetBox Blue
(http://netboxblue.com/)




-- No attachments (even text) are allowed --
-- Type: image/jpeg
-- File: newlogo.jpg
-- Desc: newlogo.jpg


-- No attachments (even text) are allowed --
-- Type: image/jpeg
-- File: clip_image002.jpg
-- Desc: clip_image002.jpg


-- No attachments (even text) are allowed --
-- Type: image/jpeg
-- File: clip_image002.jpg
-- Desc: clip_image002.jpg


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: