Posts by crushdrinker06

  • Calculating Levels

    Re: Calculating Levels


    Here is the formula

    Code
    =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),"")),"%")

  • Calculating Levels

    Re: Calculating Levels


    I believe this is what you want

    Code
    =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/

  • VLOOKUP does not return the correct values

    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

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

    Code
    =INDEX('CRM Data'!A:A,MATCH($F3,'CRM Data'!$H:$H,0))

  • Index match with part of the text as condition

    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"

  • Calculating Levels

    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.

    Code
    =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/

  • Calculating Levels

    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

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

  • Index Date

    Re: Index to left - Should be simple?


    Try

    Code
    {=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

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

  • Dance pairs combinations

    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.

  • Dance pairs combinations

    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.


    forum.ozgrid.com/index.php?attachment/69375/

  • Dance pairs combinations

    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

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

  • Join Excel tables

    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

    Code
    =VLOOKUP(F2,$A$2:$A$3,1)

    pasted in E2 and filled down.

  • Count the values by ignoring cells which contain NA

    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

    Code
    [/COLOR][COLOR=#333333]=(COUNTIF(J4:J345,>(INT(MONTH(TODAY())-2))))/(COUNTIF(J4:J345,"<>"&"NA"))[/COLOR][COLOR=#333333]

    work? Purely a guess.[/COLOR]

  • Counting numbers that appear in succession over different columns

    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/

  • add another IF to an IF, AND, OR statement

    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)

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

    Code
    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

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