Re: problem with combining functions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You're welcome!

Biff

<marcas91@xxxxxxxxx> wrote in message
news:1163763903.416599.251670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thank you so much!
Biff wrote:
=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

<marcas91@xxxxxxxxx> wrote in message
news:1163730571.979653.270870@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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

<marcas91@xxxxxxxxx> wrote in message
news:1163644271.041734.222470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Tool Offset Delta
    ... We are using a Master Plane tool setting scheme. ... indicator will work), and zero the indicator. ... "incremental" Z offset to zero. ... and reset the Z reference position. ...
    (alt.machines.cnc)
  • RE: Lookup Data in two seperate Spreadsheets
    ... the Offset function says it only returns a reference not a value. ... 'Cell Formula using the OFFSET worksheet formula with multiple criteria to ... 'Returns a reference to a range that is a specified number of rows and columns ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Nesting Address function in Offset without Quotes???
    ... The INDIRECT function will convert your text string "A1" into a cell ... is a cell reference. ... Address does the trick except that the offset function ... quotes). ...
    (microsoft.public.excel.worksheet.functions)
  • Re: macro row movement
    ... But be careful when you set an offset that you have room to reference that ... Using the down arrow key places a cell reference and this won't work ...
    (microsoft.public.excel.misc)
  • Re: problem with combining functions
    ... actually the entire array? ... The reference is not the entire range. ... MATCH,E4:E18,0)-1 tells it how many rows to offset E4 ... Because the MATCH function will only return values>=1. ...
    (microsoft.public.excel)