Lingua-Nova







erhaltlich auf Deutsch Aufbau von grossen Formeln in Excel

Information Technology

Building large formulas in Excel

Last time, having taken our survey (the explanation of what we want to do), and breaking it up into many small steps, we translated these into meta-language and finally into "Excel'ese". Now that all of these small steps are working, we are going to bring them all back together into one large formula. (In this case, Humpty-Dumpty will get put back together again.)
  • Build up a complicated [=formula] by "replacing the contents" (i.e. replacing the [=formula] in the cell with the [=formula] in the cell or cells it [=formula] refers to)

    A B C D E F
    1 Name LASTNAME Start End ## Paid Time
    2 Mary CHEATHAM 11:27 AM 1:58 PM ## 02:45   [=B28]
    3            
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    º
    22 Start 11:27 AM   [=C2]      
    23 End 1:58 PM   [=B2]      
    24 Time
    Worked
    2:31   [=B23-B22]      
    25 Hours
    Worked
    02:00  [=IF(HOUR(B24)<1;1/24;
                    HOUR(B24))]
     
    26 Minutes
    Worked
    00:31  [=IF(B24>B25;B24-B25;0)]  
    27 Minutes
    Paid
    00:45   [=VLOOKUP
                    (B26;QuarterHour;2)]
       
    28 Paid Time 02:45   [=B25+B27]      
    Quarter Hour
    00:00:00 00:00:00
    00:00:01 00:15:00
    00:15:01 00:30:00
    00:30:01 00:45:00
    00:45:01 01:00:00

  • One step at a time replace the contents of a cell with the contents of the cell it refers to
    1. F2:     02:45   [=B28]
    2. Replace [B28]
          with contents of B28:    [=B25+B27]
      F2:     02:45   [=B25+B27]
    3. Replace [B25]
          with contents of B25:    [=IF(HOUR(B24)<1;1/24;0)]
      F2:    02:45   [=(IF(HOUR(B24)<1;1/24;0))+B27]
    4. Replace [B27]
          with contents of B27:    [=VLOOKUP(B26;QuarterHour;2)]
      F2:    02:45   [=(IF(HOUR(B24)<1;1/24;0))+(VLOOKUP(B26;QuarterHour;2))]
    5. Replace [B26]
          with contents of B26:    [=IF(B24>B25;B24-B25;0)]
      F2:     02:45   [=(IF(HOUR(B24)<1;1/24;0))+(VLOOKUP(IF(B24>B25;B24-B25;0);QuarterHour;2))]
    6. Replace [B25]
          with contents of B25:    [=IF(HOUR(B24)<1;1/24;0)]
      F2:     02:45   [=(IF(HOUR(B24)<1;1/24;0))+(VLOOKUP(IF(B24>IF(HOUR(B24)<1;1/24;0);B24-IF(HOUR(B24)<1;1/24;0);0);QuarterHour;2))]
    7. Replace [B24]
          with contents of B24:    [=B23-B22]
      F2:     02:45   [=(IF(HOUR((B23-B22))<1;1/24;0))+(VLOOKUP(IF((B23-B22)>IF(HOUR((B23-B22))<1;1/24;0);(B23-B22)-IF(HOUR((B23-B22))<1;1/24;0);0);QuarterHour;2))]
    8. Replace [B23]
          with contents of B23:    [=C2]
      F2:     02:45   [=(IF(HOUR((C2-B22))<1;1/24;0))+(VLOOKUP(IF((C2-B22)>IF(HOUR((C2-B22))<1;1/24;0);(C2-B22)-IF(HOUR((C2-B22))<1;1/24;0);0);QuarterHour;2))]
    9. Replace [B22]
          with contents of B22:    [=B2]
      F2:     02:45   [=(IF(HOUR((C2-B2))<1;1/24;0))+(VLOOKUP(IF((C2-B2)>IF(HOUR((C2-B2))<1;1/24;0);(C2-B2)-IF(HOUR((C2-B2))<1;1/24;0);0);QuarterHour;2))]
    10. Now you have a complicated [=formula]
          that you can copy into rows 3, 4, 5 &c.
      F2:     02:45   [=(IF(HOUR((C2-B2))<1;1/24;0))+(VLOOKUP(IF((C2-B2)>IF(HOUR((C2-B2))<1;1/24;0);(C2-B2)-IF(HOUR((C2-B2))<1;1/24;0);0);QuarterHour;2))]

      A B C D E F
      1 ## ## ## ## ## Paid Time
      2 ## ## ## ## ## 02:45   [=(IF(HOUR((C2-B2))<1;1/24;0))+(VLOOKUP(IF((C2-B2)>IF(HOUR((C2-B2))<1;1/24;0);(C2-B2)-IF(HOUR((C2-B2))<1;1/24;0);0);QuarterHour;2))]
      3 ## ## ## ## ## 01:15   [=(IF(HOUR((C3-B3))<1;1/24;0))+(VLOOKUP(IF((C3-B3)>IF(HOUR((C3-B3))<1;1/24;0);(C3-B3)-IF(HOUR((C3-B3))<1;1/24;0);0);QuarterHour;2))]
      4 ## ## ## ## ## 01:00   [=(IF(HOUR((C4-B4))<1;1/24;0))+(VLOOKUP(IF((C4-B4)>IF(HOUR((C4-B4))<1;1/24;0);(C4-B4)-IF(HOUR((C4-B4))<1;1/24;0);0);QuarterHour;2))]
      5 ## ## ## ## ## 03:15   [=(IF(HOUR((C5-B5))<1;1/24;0))+(VLOOKUP(IF((C5-B5)>IF(HOUR((C5-B5))<1;1/24;0);(C5-B5)-IF(HOUR((C5-B5))<1;1/24;0);0);QuarterHour;2))]
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º
      º


© Copyright 2003-21 PAUL R. WILLIAMS, LINGUA-NOVA.COM
Contact: website@lingua-nova.com with your questions and suggestions concerning this site.
Websites: http://www.lingua-nova.com
http://www.oneleaf.biz
http://www.wordwatch.biz