Problem with If-then-else and string

khandu

New Member
Joined
Oct 27, 2008
Messages
24
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)
 
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.


Hmm.. so for 1:30 using HOUR(H1) gives me 1 and =TIME(0, DAY(H2)*24+HOUR(H2),0) gives me 12:01AM .. nothing gives me 1:30 :(

or 1.5 will suffice (for calculations)
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sorry, you should've been more specific. So you want the final thing to be minutes in decimals. Time converts the thing to another time variable...

Then you'd have to do:
=DAY(H1)*24+HOUR(H1)+MINUTES(H1)/60

And under format make sure you have it set to numbers.
 
Upvote 0
Sorry, you should've been more specific. So you want the final thing to be minutes in decimals. Time converts the thing to another time variable...

Then you'd have to do:
=DAY(H1)*24+HOUR(H1)+MINUTES(H1)/60

And under format make sure you have it set to numbers.

I get a #NAME? error on doing this..
 
Upvote 0
Sorry:
=DAY(H1)*24+HOUR(H1)+MINUTE(H1)/60

take out the "S"

Excellent Thanks

Now I can put this value in a new cell and then do a ifthen on it

or can I integrate this with the formula showed previously??


=IF(OR(E1="INDIA", E1="CANADA"), H1*0.15, H1*0.29)
 
Upvote 0
Lol the big formula is

=IF(OR(E3="INDIA", E3="CANADA"), (DAY(H3)*24+HOUR(H3)+MINUTE(H3)/60)*0.15,(DAY(H3)*24+HOUR(H3)+MINUTE(H3)/60)*0.29)

Thanks a bunch

One last thing.. I just realised that I don't need the H3 result to be cacluated in decimal.. but I need rounding.. so can this formula =DAY(H1)*24+HOUR(H1)+MINUTE(H1)/60 do rounding to nearest biggest number and then do the IF THEN ELSE on that number

i guess all this can go in ONE GIANT formula (as the top one?)
 
Upvote 0
Just do =ROUND( IF(********), 0) to round the number to nearest decimal. Or =ROUND(IF(******), 2) to two decimal places.
 
Upvote 0
Just do =ROUND( IF(********), 0) to round the number to nearest decimal. Or =ROUND(IF(******), 2) to two decimal places.


Where am I going wrong here

=IF(OR(E2="INDIA", E2="CANADA"), (ROUND(DAY(H2)*24+HOUR(H2)+MINUTE(H2)/60),0)*0.15,(ROUND(DAY(H2)*24+HOUR(H2)+MINUTE(H2)/60),0)*0.29)

Says too few arguments
 
Upvote 0
You placed "(" and didn't close them. They're useless anyways:
=IF(OR(E2="INDIA", E2="CANADA"), ROUND(DAY(H2)*24+HOUR(H2)+MINUTE(H2)/60,0)*0.15,ROUND(DAY(H2)*24+HOUR(H2)+MINUTE(H2)/60,0)*0.29)
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,334
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top