problem with combining functions

[marcas91]

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[Biff]

Hi!

The first argument in Offset must be a cell address. See if this does what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

 

[marcas91]

The only thing is: will the match function work if two grades can be
the same? (if you can make 2 95's then will the match function work?)

Hi!

The first argument in Offset must be a cell address. See if this does what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[marcas91]

Ok, actually in any instance I could find in my grades this worked. I
have two questions:

1. Why does putting the reference of E4 work when your reference is
actually the entire array?

2. Also, Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns the wrong value?

(Using yours, it works, THANKS SO MUCH!!!!!!!!!!)

Hi!

The first argument in Offset must be a cell address. See if this does what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[Biff]

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Why does putting the reference of E4 work when your reference is
actually the entire array?

The reference is not the entire range. The "starting point" is E4.

MATCH(SMALL(E4:E18,1),E4:E18,0)-1 tells it how many rows to offset E4

-2 tells it how many columns to offset E4.

Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns
the wrong value?

Because the MATCH function will only return values >=1. If the match value
is in the first position the result would be 1 which would mean to offset E4
by 1 row but this would lead to an incorrect reference. In that case you
want the offset to be 0 so in effect you need to "offset" the result of the
MATCH function by -1.

See Roger's reply. His solution also works and may be less complicated.

Biff

Ok, actually in any instance I could find in my grades this worked. I
have two questions:

1. Why does putting the reference of E4 work when your reference is
actually the entire array?

2. Also, Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns the wrong value?

(Using yours, it works, THANKS SO MUCH!!!!!!!!!!)

Hi!

The first argument in Offset must be a cell address. See if this does
what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[marcas91]

Thank you so much!

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Why does putting the reference of E4 work when your reference is
actually the entire array?

The reference is not the entire range. The "starting point" is E4.

MATCH(SMALL(E4:E18,1),E4:E18,0)-1 tells it how many rows to offset E4

-2 tells it how many columns to offset E4.

Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns
the wrong value?

Because the MATCH function will only return values >=1. If the match value
is in the first position the result would be 1 which would mean to offset E4
by 1 row but this would lead to an incorrect reference. In that case you
want the offset to be 0 so in effect you need to "offset" the result of the
MATCH function by -1.

See Roger's reply. His solution also works and may be less complicated.

Biff

Ok, actually in any instance I could find in my grades this worked. I
have two questions:

1. Why does putting the reference of E4 work when your reference is
actually the entire array?

2. Also, Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns the wrong value?

(Using yours, it works, THANKS SO MUCH!!!!!!!!!!)

Hi!

The first argument in Offset must be a cell address. See if this does
what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[Biff]

You're welcome!

Biff

Thank you so much!

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Why does putting the reference of E4 work when your reference is
actually the entire array?

The reference is not the entire range. The "starting point" is E4.

MATCH(SMALL(E4:E18,1),E4:E18,0)-1 tells it how many rows to offset E4

-2 tells it how many columns to offset E4.

Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns
the wrong value?

Because the MATCH function will only return values >=1. If the match
value
is in the first position the result would be 1 which would mean to offset
E4
by 1 row but this would lead to an incorrect reference. In that case you
want the offset to be 0 so in effect you need to "offset" the result of
the
MATCH function by -1.

See Roger's reply. His solution also works and may be less complicated.

Biff

Ok, actually in any instance I could find in my grades this worked. I
have two questions:

1. Why does putting the reference of E4 work when your reference is
actually the entire array?

2. Also, Why is the "rows" of the offset -1 when it's in the same
row...whenever I change it to 0, however, it returns the wrong value?

(Using yours, it works, THANKS SO MUCH!!!!!!!!!!)
Biff wrote:
Hi!

The first argument in Offset must be a cell address. See if this does
what
you want:

=OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2)

Biff

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its
corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com