excel 2000 formula

[vinimall]

can anyone help I'm new to excel formula,

I want to find a function that will help me calculate the following:

I'm a coach in athelitics, if my runners get below a certin time they
go into a group "intermediat group"

if they are above a certin time they go to the "advanced group"

I need the formula to show nothing in the speadsheet and then when a
time is entered it produces what group the athlete will be best suited
to.

Criteria:

Less than 2 minutes = Advanced group
More than 2 minutes = intermediate group
and I need the cell to show nothing untill a time is entered



Hope someone can help

 

[Anders S]

One way,

=IF(A1="","",IF(A1<TIME(0,2,0),"Advanced group","Intermediate group"))

Note
- time must be entered as Excel time e.g 00:01:55. However the display format
can be set to minutes and seconds, e.g. 01:55
- 00:02:00 (not in the criteria) will return "Intermediate group"

HTH
Anders Silvén

 

[vinimall]

Anders

Many thanks for your help the formula did the trick,

Would it be possible to help me on another formula?

How do I call the result of the cells into another cell, I mean because
the formula returns "Intermediate" or "Advanced" group how do I call
that value?

Because I do 3 tests with the Athletes and they might have 2 Advanced
results and 1 Intermediate result and I would like a formula to
calculate If they get 2 of a particular group scores they are put into
that group ie 2 Intermediate results would put them into the
Intermediate group

many thanks

vini

 

[Peter van Deelen]

Vinimall,

If Cell A1 contains the name of the athlete,

Cell B1 contains the first time,

Cell C1 contains the qualification of the first time (Advanced
group/Intermediate group).

Cell D1 contains the second time,

Cell E1 contains the qualification of the second time,

Cell F1 contains the third time,

Cell G1 contains the qualification of the third time,

Enter this formula in cell H1:

=IF (COUNTIF(C1:G1;"Intermediate group")>1;"Intermediate group";IF
(COUNTIF(C1:G1;"Advanced group")>1;"Advanced group";" "))



Good luck with your athletes, advanced or intermediate

 

[vinimall]

Anders

any thanks again, is the a way that I can change the colour of the
words Intermediate and Advanced?

Where can I learn how to write formulars is there any books you
recommend

vini

 

[Anders S]

vini,

You can use Format>Conditional Formatting to make the color (and more) change
automatically.
Specify
Cell value equals Intermediate group
and
Cell value equals Advanced group

About books, this is a common question search for "Excel books" (without the
quotes) in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.excel
end you will get many suggestions.

HTH
Anders

 

[Anders S]

Vini,

The following formula operates on columns A-C with times only:
=IF(COUNTBLANK(A11:C11)<>0,"",IF(COUNTIF(A11:C11,"<"&TIME(0,2,0))>1,"Advanced
group","Intermediate group"))

Alternatively, you can use this formula on columns A-F if you have columns for
Advanced/Intermediate rankings for each test:
=IF(COUNTBLANK(A1:F1)<>0,"",IF(COUNTIF(A1:F1,"<"&TIME(0,2,0))>1,"Advanced
group","Intermediate group"))

It's the same formula, just different range.

The formula will return empty if not all tests are done.

If they get 2 of a particular group scores they are put into
that group

Also if they get 3 of a particular group scores.

HTH
Anders Silvén

 

[Anders S]

Peter,

FYI, your formula will not work as posted on an English Excel version. The
semicolons must be replaced with commas.
The argument separator on some non-English versions (like my Swedish) is
semicolon, whereas the English separator is comma, for example
=SUMMA(A24;A25) (Swedish)
=SUM(A24,A25) (English),
so not only the function names must be translated,

Best regards,
Anders Silvén

Vinimall,

If Cell A1 contains the name of the athlete,

Cell B1 contains the first time,

Cell C1 contains the qualification of the first time (Advanced
group/Intermediate group).

Cell D1 contains the second time,

Cell E1 contains the qualification of the second time,

Cell F1 contains the third time,

Cell G1 contains the qualification of the third time,

Enter this formula in cell H1:

=IF (COUNTIF(C1:G1;"Intermediate group")>1;"Intermediate group";IF
(COUNTIF(C1:G1;"Advanced group")>1;"Advanced group";" "))



Good luck with your athletes, advanced or intermediate

 

[vinimall]

Anderds,

Is there any way I can prevent other users from deleting the formulars
I creat by mistake???

cause when they delete the info in the table they highlight it and just
press delete is there a way to protect the formulars

vini

 

[Dave Peterson]

You can lock the cells that contain formulas (and labels that you don't want the
users to change).

then you protect the worksheet.

By default, each cell is already locked. So you can select the cells that the
users should be able to change. Then Format|Cells|Protection Tab|Uncheck
Locked.

(Or you can lock cells by checking that box.)

Then when you're happy with your locked/open cells, you can protected the
worksheet. Tools|Protection|Protect sheet. You can give it a password if you
want, but it's not really secure. There are posts in the newsgroups that can
break the password quite simply.

Be aware that if you protect the worksheet, there are other things that can't be
done. (Formatting, row inserting/deleting (and column, too).) But xl2002 has a
finer level of control over these things.