SOLVED

Formula help

Copper Contributor

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

20 Replies

Hi Jeremy,

 

You can use this formula in cell F3:

=LOOKUP(E3,A2:C9)

FromTo.png

 

Please find the attached file.

 

Regards

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

SWEET, Thank you so much. I appreciate it

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

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

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

best response confirmed by Jeremy Wilkerson (Copper Contributor)
Solution

OK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.

 

Regards

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.

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:

Time Format.png

 

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?

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

AGAIN!, I say thank you sir.

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

Thanks a lot Haytham, that has been very helpful

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

Also is there a way to setup an alert to my email address from any exp dates that go red?

1 best response

Accepted Solutions
best response confirmed by Jeremy Wilkerson (Copper Contributor)
Solution

OK, I've updated the formula in the workbook to lookup the accident number instead of the accident type.

 

Regards

View solution in original post