Combining IF and VLOOKUP

[Vivek Jadav]

Hi all,

I'm trying to create a spreadsheet to analyse school test results.
What I want to do is input test results, and for Excel to do the
following:

Work out the total (this I can do)
Find the difference between this total and a previous total (this I
can also do)
Look to grade boundaries elsewhere on the spreadsheet (or on another
sheet in the same file) and work out what grade that individual got.

So, for example. John gets 67% on an A*-B paper, as opposed to a C to
E paper and an F to G paper. So there are 3 (or more) different tiers
to choose from. Therefore, I want excel to do the following, in
somewhat Excel speak:

If tier is equal to A*-B, then look at higher grade boundaries. If
tier is not equal to this, look at C to E grade boundaries. If tier is
not equal to this, look at F-G grade boundaries. THEN, IF score is
between 60-70, B. If score is between 70-80, A. If score is 80 or
above, A*.

The main thing is that I want to be able to do this through the
formula builder (on my mac) or the windows equivalent. So I've found
the IF and vLookup functions, but I simply don't know how to combine
them.

Any help would be great, thanks.

 

[Don Guillett Excel MVP]

Hi all,

I'm trying to create a spreadsheet to analyse school test results.
What I want to do is input test results, and for Excel to do the
following:

Work out the total (this I can do)
Find the difference between this total and a previous total (this I
can also do)
Look to grade boundaries elsewhere on the spreadsheet (or on another
sheet in the same file) and work out what grade that individual got.

So, for example. John gets 67% on an A*-B paper, as opposed to a C to
E paper and an F to G paper. So there are 3 (or more) different tiers
to choose from. Therefore, I want excel to do the following, in
somewhat Excel speak:

If tier is equal to A*-B, then look at higher grade boundaries. If
tier is not equal to this, look at C to E grade boundaries. If tier is
not equal to this, look at F-G grade boundaries. THEN, IF score is
between 60-70, B. If score is between 70-80, A. If score is 80 or
above, A*.

The main thing is that I want to be able to do this through the
formula builder (on my mac) or the windows equivalent. So I've found
the IF and vLookup functions, but I simply don't know how to combine
them.

Any help would be great, thanks.

"If desired, send your file to (e-mail address removed) I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Don Guillett Excel MVP]

Hi all,

I'm trying to create a spreadsheet to analyse school test results.
What I want to do is input test results, and for Excel to do the
following:

Work out the total (this I can do)
Find the difference between this total and a previous total (this I
can also do)
Look to grade boundaries elsewhere on the spreadsheet (or on another
sheet in the same file) and work out what grade that individual got.

So, for example. John gets 67% on an A*-B paper, as opposed to a C to
E paper and an F to G paper. So there are 3 (or more) different tiers
to choose from. Therefore, I want excel to do the following, in
somewhat Excel speak:

If tier is equal to A*-B, then look at higher grade boundaries. If
tier is not equal to this, look at C to E grade boundaries. If tier is
not equal to this, look at F-G grade boundaries. THEN, IF score is
between 60-70, B. If score is between 70-80, A. If score is 80 or
above, A*.

The main thing is that I want to be able to do this through the
formula builder (on my mac) or the windows equivalent. So I've found
the IF and vLookup functions, but I simply don't know how to combine
them.

Any help would be great, thanks.

dguillett @gmail.com