Apr 20 2018
04:56 AM
- last edited on
Jul 25 2018
11:45 AM
by
TechCommunityAP
Apr 20 2018
04:56 AM
- last edited on
Jul 25 2018
11:45 AM
by
TechCommunityAP
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
Apr 20 2018 05:30 AM
Hi Jeremy,
You can use this formula in cell F3:
=LOOKUP(E3,A2:C9)
Please find the attached file.
Regards
Apr 20 2018 06:06 AM
How would I do it for the attached spreadsheet? I want the cells in column F reflect what is in column D
Apr 20 2018 06:33 AM
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
Apr 26 2018 07:55 AM
Can some one tell me the formula to get what is in cell E3 on the Violation Tracker which would be inputted from column A on the Preventable Accidents Tab and then have the points that are assessed to that number added into the formula for cell G on the Violation Tracker Tab? I want the points assessed to include the points from the points assessed conditions and preventable accidents if that is even possible. Thanks
Apr 26 2018 09:06 AM - edited Apr 26 2018 09:14 AM
Hi Jeremy,
I've updated the workbook with the possible solution!
Please test it, and refer back to me if you have any further concerns.
Regards
Apr 26 2018 11:55 AM
Thanks for all your help with this Sir. Is there any way you can set it up so that under Accident I can put 1-20 depending on the accident/incident and then those points would still go in the points assessed column, without doing anything with the speed limit/actual speed columns? Thanks
Apr 26 2018 08:14 PM
SolutionOK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.
Regards
Apr 27 2018 07:12 AM
You are the man!! I wish I was smart enough to know all that, but at least I was smart enough and ask for help, and got such a great helper. Thanks a lot, again.
Oct 12 2018 12:07 PM
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
Oct 12 2018 08:36 PM
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
Oct 15 2018 05:58 AM
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?
Oct 15 2018 06:13 AM
Of course, you do that.
Just change the formula in cell D21 to this:
=TEXT(D14*24,"0.00")
And then, copy it to the right until cell J21.
After that, use this formula in cell K21 to get the total.
=SUMPRODUCT(VALUE(D21:J21))
Please find the attached file
Oct 25 2018 06:01 AM
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
Oct 25 2018 07:28 AM
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
Feb 19 2019 08:19 AM
Hi Haytham,
What I'm wanting to do is have the Exp dates color coded by dates out. So for example, if the exp date is within a month of the current date it would be red, if the exp date is 1-3 months away from current date, if between 4-12 months away it would be blue and greater than 12 months away would be green. any help would be appreciated. Thanks
Feb 19 2019 08:20 AM
Also is there a way to setup an alert to my email address from any exp dates that go red?
Apr 26 2018 08:14 PM
SolutionOK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.
Regards