Log In
Register
Home
December 8, 2017
Offline
Hi,
In Chapter 12_Data.xlsx workbook attached below , like to learn how to write If formula for Q. 3 on Sheet 1 from Data on OMT_DATA
Mitul
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
It seems to me you are doing some training, so instead of giving you the answer, please do try to write an IF formula and then let's see if you need any help.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hi Mitul,
Any progress with the formula?
/Anders
December 8, 2017
Offline
Hello Anders;
Yes, there is Progress with IF formula.
In workbook attached, in Column H, there is
=IF(AND(OR(D8="Web",D8 = "Email"),AND(G8<=17,G8>17)),"Work","Home") for hours greater than 17 and it gives me correct
output which is home , and for
Work output there is =IF(AND(OR(D9="Web",D9 = "Email"),OR(G9<=17,G9>17)),"Work","Home"), so I like to learn can this
formula be same as "home" output formula ? I tried few times, but cannot figure it out. Can you Please help me ?
Thank you very much.
Have a great day.
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
I don't understand why you included column D in your formula.
The question is: "Do our customers shop at work (most likely between 8:00 and 17:00) or at home?"
To answer that question you only need to know at what time the order was made, not how.
One other thing that needs be pointed out here is how to work with date and time.
I don't know who have created a custom number format for the time data in column G, but even though the time in
column G is shown as hours by using custom number format, it does not mean that the value in the cells are just
in hours. As I try to show in attached file, in columns I, J and K I have added some extra controls.
In I8 I am checking if the data in G8 is equal to 22 or not, using following formula:
=IF(G8=22,"G8 is 22","G8 is not 22")
As you can see, the result is that the data in cell G8 is not equal to 22, even though 22 is shown.
In J8 I have take the time value from cell G8 and convert it to just hours, using following formula:
=HOUR(G8)
As you can see, the value shown is 22.
In K8 I run the same check as I did in I8, just checking the value in cell J8 instead.
=IF(J8=22,"J8 is 22","J8 is not 22")
And as you can see, I now get that the value in cell J8 is equal to 22.
In following cells down to row 14 I am doing another check.
In cells I9 to I14 I am checking if the values in cells G9 to G14 is greater than (gt) or less than (lt) 17.
=IF(G9>17,"G"&ROW(G9)&" is gt 17","G"&ROW(G9)&" is lt 17")
The result for all cells is that all the data is less than 17, even though one row is showing 22.
In cells K9 to K14 I do the same check but now against the cells in column J, which shows the hours.
=IF(J9>17,"J"&ROW(G9)&" is gt 17","J"&ROW(J9)&" is lt 17")
And now we see that in cell K14 it is now showing that the value in cell J14 is greater than 17.
What I am trying to point out here is that you need to ensure that you work with correct data.
A hint, when you have correct hour data, check if the hour is less than 8 OR greater than 17 (you can of course
check if the hour is greater than 7 and less than 18). If you don't want to create an extra hour column, you can
of course have the hour conversion within your formula.
Good luck.
/Anders
1 Guest(s)
