formula for golf league almost there need more help

  • Thread starter Thread starter picktr
  • Start date Start date
P

picktr

the formula:

((AVERAGE(G3:I3))-35)*.80

NOW

I NEED (G3:I3) TO BE A RANGE OF CELLS
THAT CALCULATES ONLY THE LAST THREE CELLS
(WITH NUMBERS) ENTERED.

SOME CELLS MAY HAVE TEXT IF A PLAYER IS
ABSENT. SO IT SEARCHES BACK STARTING WITH
THE MOST RECENT SCORE AND PICKS UP ONLY 3 NUMBERS
TO AVERAGE.

THIS IS A PAIN, BUT IT IS FUN!

(e-mail address removed)
 
Do you even bother to read the answers you get to earlier posts?
If you did you would see that I have given you formula for averaging the
3 last cells in K3:Z3.
Finally please refrain from posting in caps

here is the formula

=AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))

entered with ctrl + shift & enter

applied to the handicap would be


=(AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))-35)*0.8
 
When I answered him in the misc. group yesterday Peo, there was no mention
of there being the possibility of text existing within the row, as he has
stated here.

Question for you:

Why do you use "Index(3:3"?
When I went to check out your formula, I received a "Circular" error when
placing it in the same row.
Is there a reason you're not using "Index(K3:Z3"?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Do you even bother to read the answers you get to earlier posts?
If you did you would see that I have given you formula for averaging the
3 last cells in K3:Z3.
Finally please refrain from posting in caps

here is the formula

=AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))

entered with ctrl + shift & enter

applied to the handicap would be


=(AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))-35)*0.8
 
Because column and row always count from the first column/row, you can use
A3:Z3 instead of K3:Z3 or else you will
get a div error. I don't get a circular error if I put my formula in for
instance AA3 using 3:3

=AVERAGE(Z3:INDEX(A3:Z3,LARGE(COLUMN(K3:Z3)*(ISNUMBER(K3:Z3)),3)))

would work for text entries not that I have any the OP will find the answer,
maybe a
crash course in net etiquette would help?

--

Regards,

Peo Sjoblom

RagDyeR said:
When I answered him in the misc. group yesterday Peo, there was no mention
of there being the possibility of text existing within the row, as he has
stated here.

Question for you:

Why do you use "Index(3:3"?
When I went to check out your formula, I received a "Circular" error when
placing it in the same row.
Is there a reason you're not using "Index(K3:Z3"?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Do you even bother to read the answers you get to earlier posts?
If you did you would see that I have given you formula for averaging the
3 last cells in K3:Z3.
Finally please refrain from posting in caps

here is the formula

=AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))

entered with ctrl + shift & enter

applied to the handicap would be


=(AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))-35)*0.8
Click to expand...
 
Peo,

Thank you for your help.

I've got this formula to work.
=(AVERAGE(Z3:INDEX(3:3,LARGE(COLUMN(K3:Z3)*(K3:Z3<>""),3)))-35)*0.8

but can't get this formula, to exclude text, to work

=AVERAGE(Z3:INDEX(A3:Z3,LARGE(COLUMN(K3:Z3)*(ISNUM
BER(K3:Z3)),3)))

I need the 1st formula to exclude text, "skip a cell" with text
and pick up the next with numbers, only 3 cells worth.

My apologies for the "caps", I'm new to this.
Could you tell?

Thanks,
Tom Pickett (e-mail address removed)
 
It works for me,

what happens when you enter it with ctrl + shift & enter

it will disregard text and only sum 3 last numeric values

note that it will not work if part of the cell is number and part of the
cell is text

=AVERAGE(Z3:INDEX(A3:Z3,LARGE(COLUMN(K3:Z3)*(ISNUMBER(K3:Z3)),3)))

I just tested it with the formula in AA3,
U3 holds 70, V3 holds 75, W3 holds "text1", X3 holds "text2" and Y3 holds 80

formula returns 75 as expected..
 
If that's your email address I can email my test sheet?

--

Regards,

Peo Sjoblom

Peo Sjoblom said:
It works for me,

what happens when you enter it with ctrl + shift & enter

it will disregard text and only sum 3 last numeric values

note that it will not work if part of the cell is number and part of the
cell is text

=AVERAGE(Z3:INDEX(A3:Z3,LARGE(COLUMN(K3:Z3)*(ISNUMBER(K3:Z3)),3)))

I just tested it with the formula in AA3,
U3 holds 70, V3 holds 75, W3 holds "text1", X3 holds "text2" and Y3 holds 80

formula returns 75 as expected..
Click to expand...
 
Peo,

It's those little things that I need to pay attention to.

how do I enter that formula using

ctrl + shift & enter?

Yes, you can email me at (e-mail address removed)

Thanks,

To
 
As you can now tell, it's not really a *little* thing to properly enter an
array formula using CSE.

Select the cell containing the formula and *either* click in the formula
bar, or hit <F2>.
Now, hold down both <Ctrl> and <Shift>, and hit <Enter>.

If done correctly, the formula will *automatically* be enclosed in curly
"{}" brackets.

Going forward, every time you revise/edit the formula, you must repeat the
same keystrokes.
An array formula is easily identified as such, because it is enclosed in
these curly brackets.

You *cannot manually* perform this enclosure.
It *must* be done *automatically* by XL to be valid!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Peo,

It's those little things that I need to pay attention to.

how do I enter that formula using

ctrl + shift & enter?

Yes, you can email me at (e-mail address removed)

Thanks,

Tom
 
Peo,

I got it. A little time is all I needed.

May have ?'s down the road.

Will try on my own for awhile.

Again, thanks for your help!!!!!

To
 
Back
Top