PDA

View Full Version : Expanding IF formula



perhol
01-10-2008, 05:20 AM
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.

=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:

=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:

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

but get error.

Bob Phillips
01-10-2008, 05:38 AM
Is this what you mean?



=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)

perhol
01-10-2008, 07:35 AM
I've got an error using it.

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

And part of the formula ",Uge1" was highlighted

perhol
01-10-2008, 07:50 AM
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.

Bob Phillips
01-10-2008, 08:09 AM
Sorry about the comma.

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



=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))

perhol
01-10-2008, 09:49 AM
Got some errors trying to use your formula. Tryed to change it like this:
=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 opposit (i got "0-0 700" when Uge1!F4 was equal to Rulleplan!D4) , so i chanced it to:
=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 it worked just like your previos formula

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

perhol
01-10-2008, 10:01 AM
Maybe i can explain what i want another way.
This is the original formula:
=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:
HVIS(ELLER(ER.TOM(Uge1!D4);(ER.TOM(Uge1!E4)));"";
determine that if Uge1!D4 or Uge1E4 is emty, the target celle schould be empty too.

This part:
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 wayes 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 emty to put only the content of Uge1!F4 in the target cell.

perhol
01-10-2008, 10:03 AM
Sorry for the look of my reply, tryed to make it look like this


Formula:

=HVIS ....

perhol
01-10-2008, 10:04 AM
How do you do that


Code:

=HVIS .....




Nihil sim

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

perhol
01-10-2008, 10:44 AM
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

Bob Phillips
01-10-2008, 11:27 AM
=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



=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))

perhol
01-10-2008, 01:11 PM
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:
=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.

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)

perhol
01-10-2008, 02:00 PM
I have been fiddeling with the formula, this is what i have until now:

=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.

Bob Phillips
01-10-2008, 02:56 PM
You've made a right mess of that haven't you?

perhol
01-10-2008, 03:38 PM
Yes, i think so.

Bob Phillips
01-10-2008, 04:06 PM
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.

perhol
01-10-2008, 06:55 PM
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:

=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

Bob Phillips
01-11-2008, 03:47 AM
Here we go then my friend



=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



=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 chnage the Text formats, I use h and H:mm for hours and hours and minutes. Danish might be other letters.

perhol
01-11-2008, 05:28 AM
I was just about to show you that the formula did not do what it was supposed to do, i got som reel strange results.

Then i saw your last line, changed the h and H:mm to t and t:mm and that is it.

It works :clap:

Thank You :thumb

Some day i will stand like this again :banghead:

Then i will just remember that there are people like You and i will end up like this :hi:

Bob Phillips
01-11-2008, 11:05 AM
I should have figured that, becuase I recall that Danish for Hour is Time isn't it? Mea culpa.