Expanding IF formula

Printable View

Show 40 post(s) from this thread on one page

01-10-2008, 05:20 AM
perhol

Expanding IF formula

I am using this formula to get data from the sheet 'Uge1', but only if no one of the cells 'Uge1!D4' and 'Uge1!E4' are empty.

Code:

=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)

And here it is translated with my best school- english:

Code:

=IF(OR(IS.EMPTY(Uge1!D4);(IS.EMPTY(Uge1!E4)));"";IF(MINUTE(Uge1!D4)>0;HOUR(Uge1!D4)&"."&MINUTE(Uge1!D4);HOUR(Uge1!D4))&"-"&IF(MINUTE(Uge1!E4)>0;HOUR(Uge1!E4)&"."&MINUTE(Uge1!E4);HOUR(Uge1!E4))&" "&Uge1!F4)

Is it possible to expand the conditions so that if the cell 'Uge1!F4' is different from 'Rulleplan!D4', then target cell is not left empty (the part og formula after ;""; is getting the data)?

I have been trying this:

Code:

=IF(OR(IS.EMPTY(Uge1!D4);(IS.EMPTY(Uge1!E4)));Uge1!F4=Rulleplan!D4;"";IF(MIN ....

but get error.
01-10-2008, 05:38 AM
Bob Phillips

Is this what you mean?

Code:

=HVIS(OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4)),Uge1!F4=Rulleplan!D4);""; HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)
01-10-2008, 07:35 AM
perhol

I've got an error using it.

AND(Logic1;[Logic2];...)

And part of the formula ",Uge1" was highlighted
01-10-2008, 07:50 AM
perhol

Changed comma to semicolon, and it works.
Only now i get "0-0 700" (the "700" is what i want).
It seems that when Uge1!D4 and Uge1!E4 is empty, it results in zeroes.
I would like, when Uge1!D4 and Uge1!E4 is empty, but Uge1!F4 is different from Rulleplan!D4, only the content of Uge1!F4 is entered in the targetcell.
01-10-2008, 08:09 AM
Bob Phillips

Sorry about the comma.

This assumes that you want the not equal test to take precedence

Code:

=HVIS(OG(Uge1!F4<>Rulleplan!D4,Uge1F4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4)));""; HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4))
01-10-2008, 09:49 AM
perhol

Got some errors trying to use your formula. Tryed to change it like this:

Code:

=HVIS(OG(Uge1!F4<>Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)

Worked just like before, except directly opposite (i got "0-0 700" when Uge1!F4 was equal to Rulleplan!D4) , so i chanced it to:
[VCode]=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));" ";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4)) &"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4)) &" "&Uge1!F4)[/Code]
and it worked just like your previos formula
01-10-2008, 09:53 AM
Bob Phillips

If F4 <> Rulleplan!D4 AND neither D4 nor E4 is blank, should it show F4 or the concatenated D4/E4 result?
01-10-2008, 10:01 AM
perhol

Maybe i can explain what i want another way.
This is the original formula:

Code:

=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)

This part:

Code:

HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";

determine that if Uge1!D4 or Uge1E4 is empty, the target cells should be empty too.

This part:

Code:

HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4)

put data in target cell different ways depending on minutes is zeroes or not and then add the content of Uge1!F4.

What i want is, when Uge1!F4 is different from Rulleplan!D4 but Uge1!D4 or Uge1E4 is empty to put only the content of Uge1!F4 in the target cell.
01-10-2008, 10:03 AM
perhol

Sorry for the look of my reply, tryed to make it look like this

Formula:

=HVIS ....
01-10-2008, 10:04 AM
perhol

How do you do that

Code:

=HVIS .....

Nihil sim
01-10-2008, 10:17 AM
Bob Phillips

Yes, we know that, but that condition could be met when D4 and E4 are also not blank, so which takes precedence?
01-10-2008, 10:44 AM
perhol

If Uge1!F4 = Rulleplan!D4 AND either Uge1!D4 or Uge1!E4 is blank, it should show blank
If Uge1!F4 = Rulleplan!D4 AND either Uge1!D4 or Uge1!E4 is blank, it should show only the content of Uge1!F4.
If both Uge1!D4 and Uge1!E4 is filled the rest of the code show the content of Uge1!D4 and Uge1!E4 and Uge1!F4
01-10-2008, 11:27 AM
Bob Phillips

Code:

=HVIS(Uge1!F4<>Rulleplan!D4;Uge1!F4;HVIS(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4));""; HVIS(MINUTE(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUTE(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUTE(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUTE(Uge1!E4);TIME(Uge1!E4))&" "&Uge1!F4))

or, in English

Code:

=IF(Uge1!F4<>Rulleplan!D4,Uge1!F4,IF(OR(ISBLANK(Uge1!D4),ISBLANK(Uge1!E4)),"", IF(MINUTE(Uge1!D4)>0,HOUR(Uge1!D4)&"."&MINUTE(Uge1!D4),HOUR(Uge1!D4))&"-"&IF(MINUTE(Uge1!E4)>0,HOUR(Uge1!E4)&"."&MINUTE(Uge1!E4),HOUR(Uge1!E4))&" "&Uge1!F4))
01-10-2008, 01:11 PM
perhol

If "Uge1!F4<>Rulleplan!D4" is true the formula now shows only Uge1!F4 even if data is entered in both Uge1!D4 and Uge1!E4.

If i use:

Code:

=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));""; HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4); TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4); TIME(Uge1!E4))&" "&Uge1!F4)

and both Uge1!D4 and Uge1!E4 is blank the formula shows "0-0 700", where the zeroes an the - comes from Uge1!D4 and Uge1!E4.

Maybe the rest of the formula should be changed. The first part seemes to do what it is supposed to do.

Code:

HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4); TIME(Uge1!E4))&" "&Uge1!F4)
01-10-2008, 02:00 PM
perhol

I have been fiddling with the formula, this is what i have until now:

Code:

=HVIS(OG(Uge1!F4=Rulleplan!D4;OG(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4))));""; HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);HVIS(TIME(Uge1!D4)>0;(TIME(Uge1!D4))&"-") &HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);HVIS(TIME(Uge1!E4)>0;(TIME(Uge1!E4)) &HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1!F4;" /"&Uge1!F4))))))

If i enter data in Uge1!D4 AND Uge1!E4 the result is precisely what i want, whether i change Uge1!F4 or not.

If i do not enter data in Uge1!D4 AND Uge1!E4 and change Uge1!F4 i get FALSEFALSE in the targetcell.

If i do not enter data in Uge1!D4 AND Uge1!E4 and do not change Uge1!F4 i get blank in the targetcell (of course).

Hope it helps, it did not help me.
01-10-2008, 02:56 PM
Bob Phillips

You've made a right mess of that haven't you?
01-10-2008, 03:38 PM
perhol

Yes, i think so.
01-10-2008, 04:06 PM
Bob Phillips

Compile a list of all possible variations of Uge1!D4 Blank/Not Blank, Uge1!F4 Blank/Not Blank, and Uge1!F4=/<>Rulleplan!D4 (I make that 8 variations), and then say what the result should be. I'll take a look tomorrow then.
01-10-2008, 06:55 PM
perhol

I will try.

The workbook is calculating workinghoures for the employees and give a visible look at the covering of needed employees.
The cells we are working on here is for 1 employee (me).
U1!D4 and U1!E4 is in the format [t]:mm
U1!F4 is standardformat
Target cell is standardformat

I have made a little change (non destructive :think: ) in the formula so it looks like this:

Code:

=HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";_ HVIS(MINUT(Uge1!D4)>0;TIME(Uge1!D4)&"."&MINUT(Uge1!D4);TIME(Uge1!D4))&"-"&HVIS(MINUT(Uge1!E4)>0;TIME(Uge1!E4)&"."&MINUT(Uge1!E4);TIME(Uge1!E4))_ &HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1!F4;" /"&Uge1!F4)))

Here is the list (RP! = Rulleplan!:

Uge1!D4=Empty | Uge1!E4=Empty | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Empty | Uge1!E4=Empty | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Filled | Uge1!E4=Empty | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Filled | Uge1!E4=Empty | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Empty | Uge1!E4=Filled | Uge1!F4=RP!D4 | Target cell = Empty
Uge1!D4=Empty | Uge1!E4=Filled | Uge1!F4<>RP!D4 | Target cell = Uge1!F4
Uge1!D4=Filled | Uge1!E4=Filled | Uge1!F4=RP!D4 | Target cell = Uge1!D4&-&Uge1!E4
Uge1!D4=Filled | Uge1!E4=Filled | Uge1!F4<>RP!D4 | Target cell = Uge1!D4&-&Uge1!E4& /&Uge1!F4

Example:

Rulleplan!D4=700

Uge1!D4 [Empty] | Uge1!E4 [Empty] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [Empty] | Uge1!E4 [Empty] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [7:00] | Uge1!E4 [Empty] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [7:00] | Uge1!E4 [Empty] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [Empty] | Uge1!E4 [15:00] | Uge1!F4 [700] | Target Cell [Empty]
Uge1!D4 [Empty] | Uge1!E4 [15:00] | Uge1!F4 [900] | Target Cell [900]
Uge1!D4 [7:00] | Uge1!E4 [15:00] | Uge1!F4 [Empty] | Target Cell [7-15]
Uge1!D4 [7:00] | Uge1!E4 [14:30] | Uge1!F4 [Empty] | Target Cell [7-14.30]
Uge1!D4 [7:00] | Uge1!E4 [15:00] | Uge1!F4 [700] | Target Cell [7-15 /700]
Uge1!D4 [7:00] | Uge1!E4 [14:30] | Uge1!F4 [900] | Target Cell [7-14.30 /900]

The part of the formula that makes the U1!F4-part of the targetcell look different
("900" or "7-14.30" or "7-14.30 /900") is this:
&HVIS(ER.TOM(Uge1!F4);"";HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));Uge1 !F4;" /"&Uge1!F4)))

Tried to make the listings look like tables. Not easy in here
01-11-2008, 03:47 AM
Bob Phillips

Here we go then my friend

Code:

=HVIS(ELLER(ER.TOM(Uge1!D4);ER.TOM(Uge1!E4));HVIS(Uge1!F4=Rulleplan!D4;"";Uge1!F4); TEKST(Uge1!D4;HVIS(MINUT(Uge1!D14)=0;"h";"h:mm"))&"-"& TEKST(Uge1!E4;HVIS(MINUT(Uge1!E4)=0;"h";"h:mm"))& HVIS(ER.TOM(Uge1!F4);"";" /"&Uge1!F4))

In English

Code:

=IF(OR(ISBLANK(Uge1!D4),ISBLANK(Uge1!E4)),IF(Uge1!F4=Rulleplan!D4,"",Uge1!F4), TEXT(Uge1!D4,IF(MINUTE(Uge1!D14)=0,"h","h:mm"))&"-"& TEXT(Uge1!E4,IF(MINUTE(Uge1!E4)=0,"h","h:mm"))& IF(ISBLANK(Uge1!F4),""," /"&Uge1!F4))

You might need to change the Text formats, I use h and H:mm for hours and hours and minutes. Danish might be other letters.

Show 40 post(s) from this thread on one page