Fixed figure resulting from a time range

[Khaldon]

I need please a formula that will achieve the following result for the time range provided below:

If cell K60 sum time value is less than 62:24 = 0
62:24-67:00 = 1121
67:01-70:54 = 1682
70:55-74:48 = 2524
74:49-78:00 = 3365
Above 78:00 = 4207

Example:
If cell K60 sum time value is 73:01, needed formula result should be 2524.[/B][/B]

[Fotis1991]

Hi

One way is to use reference cells and IF(AND...... functions...

Example E1=70:55 E2=74:48

=IF(AND(K60<F52,K60>=E52),2524,0)

This gives you 2524

Same way add the others conditions..

=IF(AND(K60<F52,K60>=E52),2524,if(and.......)

Is this works for you?

[Khaldon]

Cell C59=62:24 D59=67:00 E59=70:54 C60=74:48 D60=78:00

=IF(AND(K60<C59),0,IF(AND(K60>C59,K60=D59),1121,IF(AND(K60>D59,K60=E59),1682,IF(AND(K60>E59,K60=C60),2254,IF(AND(K60>C60,K60=D60),3365,IF(AND(K60>D60),4207,0))))))

K60 SUM Time value is 63:55

The formula resulted in 0.00 insted of 1121!

What did i do wrong please.

[Fotis1991]

see this

=IF(AND(K60<C59),0,IF(AND(K60>C59,K60<=D59),1121,IF(AND(K60>D59,K60=E59),1682,IF(AND(K60>E59,K60=C60) ,2254,IF(AND(K60>C60,K60=D60),3365,IF(AND(K60>D60),4207,0))))))

Same and for the others conditions. K60 must be >= or <= from times in Columns c-d-e

[kvsrinivasamurthy]

TRy this

=INDEX({0,1121,1682,2524,3365,4207},MATCH(K60,{0,2.6,2.79,2.95,3.12,3.25}))

[Khaldon]

It working just fine now, thank you all.