Date to count as one in formula

[oxicottin]

If I enter a date in AF4 I need it to count as 1 and I want to multiply it by AC4 and my formula is going to be a few of these adding them
together then I want to *100

Like this...

=((AF4*AC44)+(AF5*AC5)+(AF6*AC6)+(AF7*AC7))*100

But how do I get it to count the date as a number in the AF column?

 

[Joe4]

I think Im not understanding your "must be the same size"...

Same number of rows and columns.

In this last attempt,
AP22:AQ33 is 2 columns, 12 rows
D5:H16 is 5 columns, 12 rows

So you still aren't quite there yet.

 

[oxicottin]

Joe4, I had to once again change the way I needed to do things because now I needed to add in they have a max number of times. Anyway, I need help with this new formula... I'm going to try and explain it the best I can.

An employee has to perform "Safety Activities" and each safety you perform is different and each has a "Activity %" you get and there is a activity limit on each safety activity so here is my safety activity Legend.

A30:A49 = Activity Number
B30:B49 = Activity Limit
C40:C49 = Safety Activity
AB30:AB49 = Activity %

Now where I enter my data so the total Activity % can be summed for lets say this one Employee 1.

A5:A24 Enter a Activity Number
B5:B24 Enter a Activity Limit
C5:C24 Enter a Date you completed the Safety Activity

Ok, now where I enter the data in C25 I have =100*SUMPRODUCT(AB30:AB49*(C5:C24>0)) and that's the formula from before and it wont work now. I need to have no data in the cells lets say A5, B5 and C5 I enter data and what ever corresponds to the data in the legend it will give the Activity %.

Can this be done? Can I upload my worksheet?

 

[Joe4]

Yes, I think we will need to see some sample data and expected results.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Note that some people will upload files to file sharing sites and provide the links here, but note that many people cannot or will not download files from the internet for security reasons (i.e. my workplace forbids it).

 

[oxicottin]

Ok im limited to what I can do at work so here is a link to an image of my sheet. The outcome is .9 and that's just a total of what I choose from the safety activities randomly.

https://postimg.org/image/nfrvjuv37/

 

[Joe4]

From that example, can you explain exactly how one arrives at 0.9?

 

[oxicottin]

Sure, Ok first for Employee 1 in A5 there is a 2 that means I selected Act #2 from the Safety Activities legend which the employee "Completed SOC by self" and they are limited to 2 which brings me to B5 where there is a 1. This Employee completed 1 of the two activities allowed. Now C5 is the date it was completed on.

C25 is a sum of the Act% you get for completing the activities so for the first row Employee 1 on 1/1/18 would have got 0.1% and second row they would have got .4% and so on.

One issue would be the activity limit, some your allowed to have 1 some 2 some 4 ect but when entering them up top an employee usually only performs 1 on any given date. I don't want it to give a % to the total if they have reached their activity limit.

 

[Joe4]

OK. I am at work now, which means I cannot see the image again.
So I will take a look at it tonight when I am at home and have access to that site.

 

[Joe4]

I am not following your logic:

Sure, Ok first for Employee 1 in A5 there is a 2 that means I selected Act #2 from the Safety Activities legend which the employee "Completed SOC by self" and they are limited to 2 which brings me to B5 where there is a 1. This Employee completed 1 of the two activities allowed. Now C5 is the date it was completed on.

So, if they completed one of the two activities, wouldn't that be 50%?

And for the other four records I get:
1 out of 1 = 100%
1 out of 3 = 33.3%
1 out of 2 = 50%
1 out of 4 = 25%

So I have no idea how you are getting to 0.9.

 

[oxicottin]

Ok NP let me try again...

In my image top left in the red square Employee 1 they performed a few "Safety Activities".

1) Ok, now on 1/1/18 they performed Act#2 and they performed 1 of 2 max and if you look at the Act % you get for performing that activity in AB31 its 0.1%

2) Ok, now on 1/2/18 they performed Act#3 and they performed 1 of 1 max and if you look at the Act % you get for performing that activity in AB32 its 0.4%

3) Ok, now on 1/3/18 they performed Act#4 and they performed 1 of 3 max and if you look at the Act % you get for performing that activity in AB33 its 0.1%

4) Ok, now on 1/4/18 they performed Act#2 and they performed 1 of 2 max and if you look at the Act % you get for performing that activity in AB31 its 0.1%

5) Ok, now on 1/5/18 they performed Act#6 and they performed 1 of 4 max and if you look at the Act % you get for performing that activity in AB35 its 0.2%

Now in C25 total those percent's up and you get .9

 

[oxicottin]

I was also thinking to the above reply and your last question. for each time they complete that activity and get that Act % they can only get the % up to when they reach their max. For instance example 1 and 4 are from the same activity and that activity has a max you can perform twice so if they perform it again it wouldn't give them a % because they reached max.