using "IF" function

Wearb

In column K, I have this formula "=C2-C1". I then filled down, whereby each number increases by one. The K column is formatted for time.
What I want to do is edit this formula to contain an "IF" statement. What I want is to keep the formula as it is, unless the corresponding row in column "B" contains the text "ON". When that happens I would like the corrosponding cell in "K" to ignore the existing formula and insert the time 00:00:01 Any cell in column "B" that has "On" will also have a coloured cell if that is any help.

As an example: lets take row 100; "B100" contains "ON" in a red cell. I would like "K100" to then have 00:00:01. If there is any thing else in "B", I want K to have its original "=C100-C99" formula.

Taltos

Assuming K cell ref is same as C2, eg K2.

=if(B2="On","",C2-C1)

no use here for formatting, but if you have the logic of the formatting you could insert this in the formula
eg
=if(B2="On","",IF(otherfield="othercriteria","",C2-C1))

this is equals not contains.

Taltos

=IF(ISERROR(FIND("On",B2))=TRUE,"",C2-C1)

appears to be case sensitive

=IF(ISERROR(FIND("On",B2))=TRUE,IF(ISERROR(FIND("on",B2))=TRUE,"",C2-C1),C2-C1)

will check for both On and on
change as appropriate.

Wearb

Taltos wrote: »

Assuming K cell ref is same as C2, eg K2.

=if(B2="On","",C2-C1)

no use here for formatting, but if you have the logic of the formatting you could insert this in the formula
eg
=if(B2="On","",IF(otherfield="othercriteria","",C2-C1))

this is equals not contains.

I haven't had a chance to try and understand your second post yet. I have been working on the one above. I am not any sort of expert on excel, but get by with the basics.
I have managed to use your =if(B2="On","",C2-C1) by inserting 00:00:01, like this =if(B2="On","00:00:01",C2-C1).
It is working well by doing that. Because I have the column formatted for hours and minutes, the seconds don't normally show, but using "=if(B2="On","00:00:01",C2-C1)" the seconds show. It isn't a big problem, but if there was a handy way around it......

Having the cell empty gives all sorts of problems in other cells. That is why I needed the 1 second in it.

Thanks for helping.

Taltos

Replace your "00:00:01"
with

time(0,0,1)

eg
from =if(B2="On","00:00:01",C2-C1)
to =if(B2="On",time(0,0,1),C2-C1)

Taltos

Per the other formula, I used the FIND as that is what I am used to, but a google search back back with SEARCH that doesn't care about the case sensitivity

if you need to use the formula where ON could be anywhere in the field.

=IF(ISERROR(SEARCH("On",B2))=TRUE,TIME(0,0,1),C2-C1)

broken down
=IF(ISERROR(SEARCH("On",B2))=TRUE,TIME(0,0,1),C2-C1)
=IF(ISERROR(SEARCH("On",B2))=TRUE
>> if Search does not find ON or On or similar it will return a #VALUE or #NA this combination of IF with ISERROR
means that where it finds an error to use your default value ,TIME(0,0,1)
>> Otherwise use the formula where it had NO error and thus found the ON ,C2-C1)

Wearb

Taltos wrote: »

Replace your "00:00:01"
with

time(0,0,1)

eg
from =if(B2="On","00:00:01",C2-C1)
to =if(B2="On",time(0,0,1),C2-C1)

Perfect! Thanks for your help.

fyi I am recording the time my heating boiler is running for compared to the time that heat is turned on. I needed to stop my excel sheet from adding time when heating was off. So having it put in 1 second is as good as having it add nothing.

Wearb

Taltos wrote: »

Per the other formula, I used the FIND as that is what I am used to, but a google search back back with SEARCH that doesn't care about the case sensitivity

if you need to use the formula where ON could be anywhere in the field.

=IF(ISERROR(SEARCH("On",B2))=TRUE,TIME(0,0,1),C2-C1)

broken down
=IF(ISERROR(SEARCH("On",B2))=TRUE,TIME(0,0,1),C2-C1)
=IF(ISERROR(SEARCH("On",B2))=TRUE
>> if Search does not find ON or On or similar it will return a #VALUE or #NA this combination of IF with ISERROR
means that where it finds an error to use your default value ,TIME(0,0,1)
>> Otherwise use the formula where it had NO error and thus found the ON ,C2-C1)

I sort of understand that from reading it and could understand it properly if I used it in a sheet. I will save it and play around with it later.

Taltos

Just watch out - if you put in "Not on" - it will treat it as On...

And no worries, glad I could help.