Forum Discussion

DSHHI's avatar
DSHHI
Copper Contributor
Nov 16, 2022

divide a cell value amongst one column that begins with and another column that begins with.

So this is a little bit of a complicated one....

 

Scenario:

I have a budget sheet that is broken out by the first 2 numbers of a spec section.  I have an import sheet for another program that i would like to load my budget number to.  I would like to take the budget amount and divide it evenly (rounding up), amongst all the line items that have the same first 2 digits.

Additionally, I would like to only divide it among the installation activities for that specific spec section.

 

So, basically.....

If numbers in column I, on sheet 2, begin with the same left 2 digits in column A on sheet 1

And

items in Column D of sheet 2 begin with "INST" 

Then

Divide the corresponding column K number on sheet 1 evenly among all "INST" activities with same first 2 digits...

 

I'm pretty sure its a big mix of IF, AND, MATCH, LEFT, VLOOKUP, DIVIDE, ROUND functions.

 

Not sure if anyone can help me crack this one or not, but would be a great help.

 

dropbox link to workbook:

https://www.dropbox.com/scl/fi/wr5r3naeiuo61p2o0p4r9/formula-question.xlsx?dl=0&rlkey=qmsqwdv07761lywlf9a8jbz4n

 

I have the initial part working... I just need to get the division part 

 

=IF(AND((LEFT(I3,2)=VLOOKUP(LEFT(I3,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D3,4)="INST")),VLOOKUP(LEFT(I3,2),'Section Costs'!$A$3:$K$38,11,FALSE),"NO BDGT")

excel
Formulas and Functions

5 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 16, 2022

    DSHHI 

    =IF(MATCH(A1,$E$1:$E$3,0),VLOOKUP(A1,$E$1:$F$3,2,FALSE)/COUNTIF($A$1:$A$20,A1),"")

    Is this what you are looking for? Unfortunately I can't work with your file but in my understanding this should return the division part.

     

    • DSHHI's avatar
      DSHHI
      Copper Contributor
      Nov 16, 2022
      thats almost exactly what i have except my digits are longer than 2 so i'm trying to pull only the left 2 digits and its not liking that.... This is what i've gotten to, but it fails right at the end in the criteria part.....

      =IF(AND((LEFT(I17,2)=VLOOKUP(LEFT(I17,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D17,4)="INST")),VLOOKUP(LEFT($I17,2),'Section Costs'!$A$3:$K$38,11,FALSE)/(COUNTIF(LEFT($I$3:$I$493,2),(LEFT($I17,2)))),FALSE)
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 16, 2022

        DSHHI 

        =IFERROR(IF(MATCH(LEFT(A1,2),LEFT($E$1:$E$3,2),0),VLOOKUP(LEFT(A1,2),CHOOSE({1,2},LEFT($E$1:$E$3,2),$F$1:$F$3),2,0)/COUNTIF($A$1:$A$20,A1),""),"")

        You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. This suggestion is with VLOOKUP and CHOOSE however it should be easier with INDEX and MATCH.

Resources