Problem with If-then-else and string

[khandu]

Hi Guys

So I have two columns

E:E can contain either Canada, United, India

H:H contains some time

I am trying

=IF(E:E=iNDIA or E:E=Canada,(H:H)*0.15,(H:H)*0.29)

Does not work and gives an error "#NAME"

how to fix this?

NOTE: the formula automagically puts iNDIA instead of what I type (India)

 

[khandu]

Also H:H is in the format mm:ss... when calculating how do i make sure that it just takes it as simple number? so 1:00 should do 1*.15 OR 1:30 should do 1.30*.15 etc..

 

[mikerickson]

Try putting this into a cell in row 1 (not in column E or H) and dragging downward.

=IF(OR(E1="INDIA", E1="CANADA"), H1*0.15, H1*0.29)

 

[freesurf]

couple of things here.

- when you refer to a piece of text, e.g. India, you need to use double quotes, so "India"
- in excel, the 'or' element of your expression needs to be restated.
- lastly, you're feeding entire columns into your if statement, whereas it usually requires cell-by-cell comparisons.

Following your logic you can try the following in cell K1 (say):

IF(Or(E1="India",E1="Canada"),H1*0.15,H1*0.29)

You then drag this formula down the whole of column K.

Even simpler, but achieving the same result in K1: if(E1="United",H1*0.29,H1*0.15)

Again, you'll need to drag down.

Not sure what you're getting at with the second query, though

HTH

 

[khandu]

Try putting this into a cell in row 1 (not in column E or H) and dragging downward.

=IF(OR(E1="INDIA", E1="CANADA"), H1*0.15, H1*0.29)

Thanks a lot.. saw my mistake of OR.. i had tried quoted but then i guess the or was the issue

can u help with the 2nd issue (in the 2nd post) kinda related to this one.. does not give the right answer

 

[khandu]

Not sure what you're getting at with the second query, though

HTH

Basically the H1/2/3 ... column contains a time

it shows as 1:00 / 0:30 etc.. so I want to do maths on that..

=IF(OR(E1="INDIA", E1="CANADA"), H1*0.15, H1*0.29)

so

If Canada: then H1 (1:00)*.15 should give me .15 or H1 (6:00)*.15 should give me 0.9

But it does not due to its format in time

the format cell for h1 shows as "h:mm" but it is basically 1 minute / 6 minutes 30 seconds etc..

 

[leigao84]

Thanks a lot.. saw my mistake of OR.. i had tried quoted but then i guess the or was the issue

can u help with the 2nd issue (in the 2nd post) kinda related to this one.. does not give the right answer

You have to convert the date to a int using the MINUTE(serial) function.
Try:

=IF(OR(E1="INDIA", E1="CANADA"), MINUTE(H1)*0.15, MINUTE(H1)*0.29)

 

[leigao84]

Basically the H1/2/3 ... column contains a time

it shows as 1:00 / 0:30 etc.. so I want to do maths on that..

=IF(OR(E1="INDIA", E1="CANADA"), H1*0.15, H1*0.29)

so

If Canada: then H1 (1:00)*.15 should give me .15 or H1 (6:00)*.15 should give me 0.9

But it does not due to its format in time

the format cell for h1 shows as "h:mm" but it is basically 1 minute / 6 minutes 30 seconds etc..

Wait you have hours in there as well? Then you need to do something like:
HOUR(H1)*60*.15+MINUTE(H1)*.15

 

[khandu]

You have to convert the date to a int using the MINUTE(serial) function.
Try:

=IF(OR(E1="INDIA", E1="CANADA"), MINUTE(H1)*0.15, MINUTE(H1)*0.29)

Just realized something is wrong

so a cell which contains minutes

H2: 1:00
Format Cell: h:mm
formula bar : 1:00:00 AM

I had not put any of these settings.. i had just pasted from a PDF

how do I make excel realise its basically 1 minute and not 1 hr or time 1am?


maybe once i fix this then the formula will work ??

 

[leigao84]

Just use HOURS() instead of MINUTES() if you don't want to make any changes to the data.
If you really want to change the data then you need to do a hours to minutes conversion by formula, a new column with
=TIME(0, DAY(H1)*24+HOUR(H1),0)
Or you can simply use that for your minutes calculation.