If

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:42 PM GMT

Question

I have two coluymsn and want to find the transactiosn that qualify for same day delivery to customer. I have two columns - Transaction ID and the Time (hh:mm:sec) and the transcations that take place before 15:00:00 qualify for same day delivery and others dont.

I use the formula- IF(D4>="15:00:00",1,0) to gte 1 for thos dates which take place before 3:00 p.m. but it returns 0 for all netries. could you suggest?

Kindest Sid

Answer

You're comparing it against the text string, not the time. Use either of these: =IF(D4>=TIMEVALUE("15:00:00"),1,0) or =IF(D4>=1*"15:00:00",1,0) but this gives a 1 if the time is AFTER 3PM (your description said you wanted a 1 BEFORE 3PM. You may want to reverse the 1 & 0...?

HTH

Question

Hi Miguel, hope you can help me. I am a novice to excel. I am trying to write a formula that will give a value if a score falls with in a certain range of numbers. Scores are 5,4,3,2 and one, assigned to the ranges 32-36, 27-31, 21-26, 16-20 and 6-15 respectively. Here is the formua but it is not accepted by excel. Im in Sweden so IF is OM: =OM(C34>31,"5",OM(C34>26,"4",OM(C34>20,"3",OM(C34>15,"2",OM(C34>5,"1","0")))))

Hope you can help.

Kind Jess

Answer

In this case I would recommend to use VLOOKUP (I don't know what would be the formula in Swedish). For that, you can create a table like this on range A1:B5 on Sheet2: 6 1 16 2 21 3 27 4 32 5 And then use a formula like this, to return the score for the value on A1: =VLOOKUP(A1,Sheet2!$A$1:$B$5,2) This should return the values from the given ranges.

Advertisement

©2021 eLuminary LLC. All rights reserved.