• 411K Members
• 5,714 Online
• 466K Conversations
SOLVED

## Formula help

Occasional Contributor

# 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

20 Replies

# Re: Formula help

Hi Jeremy,

You can use this formula in cell F3:

`=LOOKUP(E3,A2:C9)`

Regards

# Re: Formula help

How would I do it for the attached spreadsheet? I want the cells in column F reflect what is in column D

# Re: Formula help

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}),"")`

Regards

# Re: Formula help

SWEET, Thank you so much. I appreciate it

# Re: Formula help

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

# Re: Formula help

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

# Re: Formula help

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

Solution

# Re: Formula help

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

Regards

# Re: Formula help

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.

# Re: Formula help

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

# Re: Formula help

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:

Regard,

Haytham

# Re: Formula help

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?

# Re: Formula help

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))`

# Re: Formula help

AGAIN!, I say thank you sir.

# Re: Formula help

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 :)

Sameh

# Re: Formula help

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.

Regards,

Haytham

# Re: Formula help

Thanks a lot Haytham, that has been very helpful

# Re: Formula help

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

# Re: Formula help

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

# Re: Formula help

Hi Jeremy,

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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies