- last edited on
I need help creating the formula for cell F3. Stating that if cell D3 is as follows:
I'm sure its a simple formula but I have no idea of what it is. Thanks for any help
04-20-2018 06:33 AM
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:
Please find the attached file.
04-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
04-26-2018 09:06 AM - edited 04-26-2018 09:14 AM
I've updated the workbook with the possible solution!
Please test it, and refer back to me if you have any further concerns.
04-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
04-26-2018 08:14 PMSolution
OK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.
04-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.
10-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
10-12-2018 08:36 PM
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:
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.
10-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?
10-15-2018 06:13 AM
Of course, you do that.
Just change the formula in cell D21 to this:
And then, copy it to the right until cell J21.
After that, use this formula in cell K21 to get the total.
Please find the attached file
10-25-2018 06:01 AM
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,
02-19-2019 08:19 AM
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
02-20-2019 11:50 AM
You can do that by setting some Conditional Formatting rules that powered by formulas.
Please find the attached file to see how these rules can be applied.
With regards to the email alerts, I'm sorry! But it's not easy to implement in Excel.
You definitely need to some VBA codes or third-party solutions to do so!
I would suggest you ask this community for that.
Hope that helps