Re: Calculating Levels
Here is the formula
=CONCATENATE("OL",IF(B8=B2,"s/0",IF(B8>B2,"i/",IF(B8<B2,"d/"))),IF(B8>B2,ROUND((B8-B2)/B8*100,2),IF(B8<B2,ROUND((B2-B8)/B2*100,2),"")),"%/","VL",IF(AND(B8=B2,ABS(B8-B2)=B11),"s/0",IF(ABS(B8-B2)=B11,"j/0",IF(ABS(B8-B2)>B11,"A/",IF(ABS(B8-B2)<B11,"S/")))),IF(ABS(B8-B2)>B11,ROUND(ABS(B8-B2)-B11/ABS(B8-B2)*100,2),IF(ABS(B8-B2)<B11,ROUND(B11-ABS(B8-B2)/B11*100,2),"")),"%")Re: Calculating Levels
I believe this is what you want
=CONCATENATE("OL",IF(B8=B2,"s/0",IF(B8>B2,"i/",IF(B8<B2,"d/"))),IF(B8>B2,CEILING((B8-B2)/B8*100,0.0001),IF(B8<B2,CEILING((B2-B8)/B2*100,0.0001),"")),"%/","VL",IF(AND(B8=B2,ABS(B8-B2)=B11),"s/0",IF(ABS(B8-B2)=B11,"j/0",IF(ABS(B8-B2)>B11,"A/",IF(ABS(B8-B2)<B11,"S/")))),IF(ABS(B8-B2)>B11,CEILING(ABS(B8-B2)-B11/ABS(B8-B2)*100,0.0001),IF(ABS(B8-B2)<B11,CEILING(B11-ABS(B8-B2)/B11*100,0.0001),"")),"%")Here is the file.forum.ozgrid.com/index.php?attachment/69604/
Re: VLOOKUP does not return the correct values
Vlookup can only search through the first column in the table. on the CRM Data page you would have to move the email to the A column.
Then in the Example page in A3 put
=VLOOKUP($F3,'CRM Data'!$A$2:$F$101,2,FALSE)If you would prefer you can leave the CRD Data sheet alone and insted in A3 put
=INDEX('CRM Data'!A:A,MATCH($F3,'CRM Data'!$H:$H,0))Re: Cell value as an formula argument
That image is unreadable. It would be better if you could give us a sample file to work with.
Re: Calulate Hours based on individual daily rates
In E3 of Sheet1 put
=IF(ISNA(MATCH(A3,Names,0)),"No Match",PRODUCT(D3,INDEX(Sheet2!D:D,MATCH(Sheet1!A3,Names,0)+4)))I have added the file forum.ozgrid.com/index.php?attachment/69446/
Re: Calulate Hours based on individual daily rates
try changing that part to PRODUCT(Sheet2!$B5,$D$3,Sheet2!$D5)
Re: Index match with part of the text as condition
First the file you attached is referencing another file. This is fine for the final file that you use, but it does make it difficult to fix issues from our side. Instead set up a dummy sheet in the file you are attaching. Second for what you are trying to do you will have to add an if statement wherever you are trying to only grab the data that matches a certain criteria. int eh code I have only changed one part, which is highlighted in yellow, but you will need to fix the rest. Please be aware that this will turn the formula into an array formula which has{} around the whole formula. In order to get this once you have finished the formula press ctrl+shift+enter. =INDEX('G:\CLGD-Techniczny\Faktury techniczny\[Faktury kosztowe.xlsm]FAKTURY DT'!M$2:M$1048576,MATCH(1,if('G:\CLGD-Techniczny\Faktury techniczny\[Faktury kosztowe.xlsm]FAKTURY DT'!J$2:J$1048576=F2,'G:\CLGD-Techniczny\Faktury techniczny\[Faktury kosztowe.xlsm]FAKTURY DT'!J$2:J$1048576))*((MID('G:\CLGD-Techniczny\Faktury techniczny\[Faktury kosztowe.xlsm]FAKTURY DT'!$D$2:$D$1048576,7,4)="2015"))*('G:\CLGD-Techniczny\Faktury techniczny\[Faktury kosztowe.xlsm]FAKTURY DT'!U$2:U$1048576=A4),0))
I hope this helps. If you want some examples of array formulas do a search in google for "Excel Array Formula"
Re: calculating previous days open level with valve level-seepage OR alien level
I believe I have figured out what you are saying. Please try this code. It matches the examples you gave.
=CONCATENATE("OL",IF(B8=B2,"s",IF(B8>B2,"i",IF(B8<B2,"d"))),"VL",IF(AND(B8=B2,ABS(B8-B2)=B11),"s",IF(ABS(B8-B2)=B11,"j",IF(ABS(B8-B2)>B11,"A",IF(ABS(B8-B2)<B11,"S")))))
I have added the file to show it in place. forum.ozgrid.com/index.php?attachment/69402/
Re: calculating previous days open level with valve level-seepage OR alien level
Since you didn't put it into terms you used in the file I put the formula in the terms used in the first post
=IF(AND(OLn=OLp,OLp=VL),"OLsVLs",IF(AND(OLn>OLp,OLp=VL),"OLiVLj",IF(AND(OLn<OLp,OLp=VL),"OLdVLj",IF(AND(OLn>OLp,OLp>VL),"OLiVLdA",IF(AND(OLn>OLp,OLp<VL),"OLiVLiS",IF(AND(OLn<OLp,OLp<VL),"OLdVLiS",IF(AND(OLn<OLp,OLp>VL),"OLdVLdA")))))))I must agree with my fellow posters about the way you phrase things being a bit confusing, but I suspect that is probably because you are too deep in the language that we are not used to.
Re: Index to left - Should be simple?
Try
{=MAX(IF(D2:D10=1,A2:A10))}
This is using an array formula. In order to enter a formula as an array formula type in the normal formula eg
=MAX(IF(D2:D10=1,A2:A10))then press Ctrl+Shift+Enter. What the formula does is goes through the LTI column and if there is a 1 in the row then it includes that row in the max formula.
Re: Dance pairs combinations
If you look at the file you can see how I was able to list all the possible combinations. Each column for the girls has different formulas. Each of the boys columns has similar formulas where it just indexes on the corresponding girl column every 24 rows. I doubt this is what you are asking about, but it was easier for me to figure it out that way. As to how I figured out what formulas to use I listed out all possible combinations for the girls and looked at each column independently looking for patterns. As to how I determined 576 possible combinations I found that there were 24 possible for the girls and that each combination of guys will go through each combination of girls. In math terms that would be 24 times 24 = 576. In order to check that Once I figured out what formulas to use I simply filled down until the boys ran out of options.
Re: Dance pairs combinations
Well, The combinations are listed in the file.
Re: Dance pairs combinations
I have modified the file heavily. Please note in this file you input in the cells that are shaded. I have made it were you can type in a number between 1 and 100 and it will give you that many random pairings. Just so you know there is 576 possible combinations. If you do not like the random pairings given then press F9 and it will give you new pairings. Once you have the pairings you like you can then copy and paste as text in a different location If this file is running too slow I can reduce the number of pairings you can receive which will speed up the file. Unfortunately the formulas do not apply themselves to to increased or decreased numbers of groups of partners in other words the formulas are not really adaptable to any number of dance pairs than 4. For that I believe you would want to go with VBA and for that you would need to find someone else to help. I hope this is what you are looking for.
Note: some of the formulas do have missing references in the upper rows in the A through H columns. This is okay. Just find the first row that isn't missing references and that will show the logic behind it.
Re: Dance pairs combinations
forum.ozgrid.com/index.php?attachment/69356/I have updated the file so that you can type in names and have them match up
Re: Dance pairs combinations
Take a look at this file. it may be what you are looking for.. The following picture just shows the excell sheet setup[ATTACH=CONFIG]69355[/ATTACH]. In D2 you would put the equation
=IF(OR(D1="",IFERROR(AND(VALUE(MID(D1,2,1))=$A$2,VALUE(RIGHT(D1,1))=$B$2),FALSE)),"",CONCATENATE("b",IF(VALUE(RIGHT(D1,1))=$B$2,VALUE(MID(D1,2,1))+1,MID(D1,2,1)),"g",IF(VALUE(RIGHT(D1,1))=$B$2,1,VALUE(RIGHT(D1,1))+1)))and then fill down. With this setup you can change the values in A2 and B2 to whatever you want and it will automatically update.
Re: Convert decimal to text fraction
Try using a custom format of # #/# . It worked when going to word and notepad.
Re: Join Excel tables
If you reverse your country and state columns in the order STATE COUNTRY at A1 and B1 respectively. Then you could use a vlookup in the second table. In ohter words if your second table was STATE CITY and you added a column before those the be COUNTRY STATE CITY in E1 through G1 respectively then you could use the formula
=VLOOKUP(F2,$A$2:$A$3,1)pasted in E2 and filled down.
Re: Count the values by ignoring cells which contain NA
Without seeing the file my only guess would b do you need the quotation marks around the (INT(MONTH(TODAY())-2)) in your formula in other words would
[/COLOR][COLOR=#333333]=(COUNTIF(J4:J345,>(INT(MONTH(TODAY())-2))))/(COUNTIF(J4:J345,"<>"&"NA"))[/COLOR][COLOR=#333333]work? Purely a guess.[/COLOR]
Re: Counting numbers that appear in succession over different columns
This may not be quite what you are looking for, but I do have a sheet that should work for you. Please note the use of the {} around the formulas in the J row. This formula uses the array formula which can be done by pressing ctrl+shift+enter after typing in the formula. the {} does fill with the formula. You can hide the H column if you wish.forum.ozgrid.com/index.php?attachment/69289/
Re: add another IF to an IF, AND, OR statement
Do you want it to be checked after or before ISBLANK(A142)? If so do you want it to be checked before or after I142 is checked for subscriber?
If before ISBLANK(A142)
=IF(I142="Trial","Trial",IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),"",$J$5&I142)))If before checking
I142 [COLOR=#000000][FONT=Inconsolata]=IF(ISBLANK([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]A142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]),,IF(I142="Trial","Trial",[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF(AND([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]L142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-TODAY()>[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],OR([/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Subscriber RENEWAL"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Subscriber NEW"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])),[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]$J$5[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]I142[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])))[/FONT][/COLOR]If after I142
=IF(ISBLANK(A142),,IF(AND(L142-TODAY()>1,OR(I142="Subscriber RENEWAL",I142="Subscriber NEW")),IF(I142="Trial","Trial",$J$5&I142))Hope this helps.