Prev: Column that needs separation
Next: Excel hyperlink within workbook and SharePoint drafts
From: wicket_wm on 19 May 2010 21:39
I need a formula to calculate the following information please: I will have
someone enter a time (ex 6:31) in cell C3. If the time matches one of the
times in cells B17-B22 I need it to display 4.6, if it matched one of the
times in cells B23-28 I need it to display 4.7, if it matches one of the
times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a
simple formula I can do for this please?
From: Fred Smith on 19 May 2010 22:04
=IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no
match")))

Can't do the "and so on" part, but hopefully you get the picture.

Regards,
Fred

"wicket_wm" <wicket_wm(a)discussions.microsoft.com> wrote in message
news:5734C90F-3E38-432F-A742-781161429E0A(a)microsoft.com...
>I need a formula to calculate the following information please: I will have
> someone enter a time (ex 6:31) in cell C3. If the time matches one of the
> times in cells B17-B22 I need it to display 4.6, if it matched one of the
> times in cells B23-28 I need it to display 4.7, if it matches one of the
> times in cells B29-34 I need it to display 4.8 and so on. Does anyone have
> a
> simple formula I can do for this please?

From: Ms-Exl-Learner on 19 May 2010 22:16
Try this...

=IF(COUNTIF(B17:B22,C3)>=1,4.6,IF(COUNTIF(B23:B28,C3)>=1,4.7,IF(COUNTIF(B29:B34,C3)>=1,4.8,"")))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"wicket_wm" wrote:

> I need a formula to calculate the following information please: I will have
> someone enter a time (ex 6:31) in cell C3. If the time matches one of the
> times in cells B17-B22 I need it to display 4.6, if it matched one of the
> times in cells B23-28 I need it to display 4.7, if it matches one of the
> times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a
> simple formula I can do for this please?
From: Teethless mama on 19 May 2010 23:38
Try this:

=ROUNDUP(MATCH(C3,B17:B100,)/60,1)+4.5



"wicket_wm" wrote:

> I need a formula to calculate the following information please: I will have
> someone enter a time (ex 6:31) in cell C3. If the time matches one of the
> times in cells B17-B22 I need it to display 4.6, if it matched one of the
> times in cells B23-28 I need it to display 4.7, if it matches one of the
> times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a
> simple formula I can do for this please?
From: Gary Keramidas on 20 May 2010 00:08
i think you mistyped an extra comma:

ROUNDUP(MATCH(C3,B17:B100)/60,1)+4.5


--


Gary Keramidas
Excel 2003


"Teethless mama" <Teethlessmama(a)discussions.microsoft.com> wrote in message
news:88E3F336-E5BC-4323-8D8D-D2E417B0EBA9(a)microsoft.com...
> Try this:
>
> =ROUNDUP(MATCH(C3,B17:B100,)/60,1)+4.5
>
>
>
> "wicket_wm" wrote:
>
>> I need a formula to calculate the following information please: I will
>> have
>> someone enter a time (ex 6:31) in cell C3. If the time matches one of the
>> times in cells B17-B22 I need it to display 4.6, if it matched one of the
>> times in cells B23-28 I need it to display 4.7, if it matches one of the
>> times in cells B29-34 I need it to display 4.8 and so on. Does anyone
>> have a
>> simple formula I can do for this please?

 | 
Pages: 1
Prev: Column that needs separation
Next: Excel hyperlink within workbook and SharePoint drafts