• 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.

How to calculate value based on one or two rows based on data in another column

soderlloyd

New Member
Hello everyone -

Thank you for your help.

I am trying to calculate an "adjusted" value (column E) based on two other columns (C & D) (sample data attached - the full data has 200+ rows).

If column C = "Total", column E = column D. Easy. However, if column C = "8350019" or "8350000" then a calculation has to be done with data from 2 rows corresponding to the same person (highlighted).

Thank you again and I'm happy to explain more if needed!

-Laurel
 

Attachments

  • test data.xlsx
    9.2 KB · Views: 3
Thank you for your response. I am trying to understand your formula, but it is very complex for me so I am not sure how to update it to help with this scenario that also happens (attached). Sometimes an employee will have 3 occurrences. Thanks!
 

Attachments

  • Copy of 1test data.xlsx
    10.8 KB · Views: 8
To clarify, in this example, I need to sum the two group IDs (8350000+8350019) and then use that value in the formula for the employee, i.e. =D25-SUM(D23:D24)+((SUM(D23:D24))/4). Thank you so much!!
 
Hi ,

You mention :
However, if column C = "8350019" or "8350000" then a calculation has to be done with data from 2 rows corresponding to the same person (highlighted).
What is this calculation ?
Then again you mention :
Sometimes an employee will have 3 occurrences.
What should be done in this case ?

It would help if instead of using an Excel formula , you can explain the outcomes in all possible cases , in plain English.

Narayan
 
64191

In E2, formula copied down :

=IF(C2="Total",D2-SUMIFS(D:D,B:B,B2,C:C,"<>Total")*3/4,"")

then,

copied to K2 and drag down.

Remark :

Please convert existing "Total Units" column from Text to Numeric value, by : Select D2:D12>> "Data" >>"Text to Columns" >>Finish

Regards
Bosco
 

Attachments

  • AdjustedUnit.xlsx
    12.4 KB · Views: 5
Back
Top