• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. M

    Formula to convert cell value based on # of digits

    mpolo954, Good afternoon. You're welcome. Thanks for the feedback and glad to help.
  2. M

    Formula to convert cell value based on # of digits

    mpolo954, Good afternoon. Greetings vletm colleague. Maybe a simple formula can solve your question. Try to use: =(LEN(A2)-LEN(SUBSTITUTE(A2;".";"")))+1 I hope it helps.
  3. M

    Index Match assign department based on employee and Job

    excelnovice2000, Good morning You're welcome. Thanks for the feedback and glad to help.
  4. M

    Index Match assign department based on employee and Job

    excelnovive2000, Good morning. Try to use: E4 --> =IF(OR(D4="Management 2021";D4="PTO 2021");INDEX(emp[Depts];MATCH(B4;emp[Employee];0));INDEX(codes[Dept];MATCH(D4;codes[Analytic Account];0))) Push it down as necessary. Is this what you want? I hope it helps.
  5. M

    Adjust the sequence equation under two conditions

    Hany ali, Good evening. You didn't tell us your intentions with the formula. A2 --> =IF(A2="";"";"1 "&IF(B2="Enterance Fees"; IFERROR(VLOOKUP(A2;$G$2:$I$4;3;FALSE);"");"")) Please, tell us if it worked for you. I hope it helps.
  6. M

    Data format

    Shabbo, To avoid having the same vehicle in the same months in several years, use: Consolidated as above >>> use Inline code <<< B6 --> =SUMPRODUCT((MONTH('Garage Expenses'!$B$2:$B$4034)&YEAR('Garage Expenses'!$B$2:$B$4034) = MONTH(B2)&YEAR(B2)) * ('Garage Expenses'!$C$2:$C$4034=A6) *...
  7. M

    Data format

    Shabbo, Good afternoon. You can use: Consolidated B6 --> =SUMPRODUCT( (MONTH('Garage Expenses'!B2:B5000)=MONTH($B$2)) * ('Garage Expenses'!C2:C5000=A6) * ('Garage Expenses'!D2:D5000)) Attention: In the Garage Expenses line 1700 there is a header line which invalidates all calculations...
  8. M

    Extract Date from Cell

    hossam sadek, Good morning. Perhaps these procedures can help. Do it: B2 --> =MID(A2; 7; FIND(" "; A2; 1) -6) Copy it down. Select the desired range in column B. --> CTRL + C --> Paster Special --> Value Taking advantage that the range is still selected do: --> Data menu -> Text to Column...
  9. M

    CLEAN ( ) to remove nonprinting characters

    Dashboardnovice, Good morning. Try to use this formula: =Trim( Clean( Substitute( C5; CHAR(160); " " ))) Tell us if it worked for you. I hope it helps.
  10. M

    A1=2^20-999

    Good Morning, Master Hui and James. I use an Excel 2003 version. If I want to do a job like James wants, I need to use a Conditional Format. Selecting all cells I need, putting formula =ROW(A2)=$A$1 and selecting my desired color to highlight the cell. I misunderstood your explanation...
  11. M

    Help with SUMPRODUCT COUNTIF

    Hi Laura, Good Morning. I believe that the question error is located at a syntax problem. You must use the same range on all clauses when using SUMPRODUCT function. =SUMPRODUCT(--(H2:H3363&#62;60),--(H2:H3363&#60;90),--(F2:F3362="John Smith")) H2:H3363 H2:H3363 F2:F3362 --&#62; 62 final...
  12. M

    Adjust end of range in a Sum formula

    Hi Blair, Good Evening. Suppose your control cell is A1.(the cell where you put the end of desired range) You can use it: =SUM(INDIRECT(CONCATENATE("C1:C",A1))) Try this one and tell us it it worked for you. Hope it helps...
  13. M

    Extracting specific numbers from a sequence and converting to year of birth

    Hi Katrinthor. Try to do this: Supposing your data are at A1 Use in B1 this formula: VALUE(CONCATENATE("19",MID(A1,5,2))) Tell us if it worked for you. I hope it can help you. Best regards, Marcilio ------------------------ Belo Horizonte, Brazil
View older results
Back
Top