Forum Discussion
Formula help
I need help creating the formula for cell F3. Stating that if cell D3 is as follows:
1-5=0
6-8=3
9-11=4
12-14=6
15-17=8
18-19=10
20-21=12
22+=15
I'm sure its a simple formula but I have no idea of what it is. Thanks for any help
OK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.
Regards
20 Replies
I' trying to get it to where I just input my time in/time out and it calculate the hour and then total across where it says hours worked then total across the bottom. Thanks
Hi Jeremy,
Formulas in cells D14 to J14 are works just fine!
To get the correct to total in cell K21, all you have to do is to multiply the total by 24 as follows:
=TEXT(SUM(D21:J21)*24,0)
NOTE: I've used TEXT function to overcome an issue that may set back the format to the time format.
You can also change the time format of cell K21 to get the correct total and keep it in time format as the below screenshot:
Please find the attached file.
Regard,
Haytham
Thank you Sir. Is there anyway to have the totals correspond as on the tab at the bottom "Hours Converted" so that say on D21 10:15 translates to 10.25?
Hi Jeremy,
You can use this formula in cell F3:
=LOOKUP(E3,A2:C9)
Please find the attached file.
Regards
Hi Haytham,
I actually tried the same formula for the attached sheet, but it does not give the correct output most of the time, I wanted to categorize the C column to different seasons depending on the range of dates, wondering if you can help me :)
Thanks in advance,
Sameh
Hi Sameh Rafik,
That's because the table of seasons in the Arrays sheet isn't sorted!
It MUST be sorted to get the correct result from the LOOKUP function.
Anyway, I've sorted it for you.
Please find the attached file.
Regards,
Haytham
How would I do it for the attached spreadsheet? I want the cells in column F reflect what is in column D
Hi Jeremy,
I've updated your workbook with this formula in cell F3:
=IFERROR(LOOKUP(D3,'Points Assessed Conditions'!$A$2:$C$9),"")
It refers to a table in a separate sheet called Points Assessed Conditions.
I did that, to make it easy for you in the future if you want to update the conditions.
If you don't want that, you can hardcode the conditions in the formula as follows:
=IFERROR(LOOKUP(D3,{1,5,0;6,8,3;9,11,4;12,14,6;15,17,8;18,19,10;20,21,12;22,0,15}),"")Please find the attached file.
Regards
