# Help with some formulas

#### zendog1960

##### Active Member
I have a spread sheet I am trying to get working. Here is the form so far:
Book1.xls
ABCDEFGHIJ
1PhilTom
2Week 1Week 2Week 3Week 1Week 1
3PhilTomA = AbsentWeek 2Week 2
6JimJimWeek 1Week 1
7TonyDaveWeek 2Week 2
8Week 3Week 3
9GuyJim
10Week 1Week 1
11Week 2Week 2
12Week 3Week 3
13TonyDave
14Week 1Week 1
15Week 2Week 2
16Week 3Week 3
Sheet1

Here is what I am trying to do. Each week, any of the 8 people shown could be present at this function. Each week we write who is there. I would like to have a formula that will place in columns H or J, an 'A' for Absent for the weeks they are not on the list; a 'P' if they are present that week and a blank if t hat week hasn't yet happened.

The weeks that have not happened yet will be blank by default but if the fumula needs to be written to force the blank, I am ok with that. I thought vlookup or match would work but that only uses the data in the arrays and doesn't allow to manually place data that isn't in the array as far as I know.

Any thoughts? This is really perplexing to me and I am sure it really is simple. I just cannot get my head around this.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Jeffrey Smith

##### Well-known Member
Countif works

The formula next to week1 below Phils name (Cell H2) should be

Code:
``=IF(COUNTIF(\$A\$3:\$A\$100,\$G\$1)>0,"X","")``

This will put an X in that cell. You can make anything you want.

Hope that's a beginning.

#### Domenic

##### MrExcel MVP
I'd suggest changing the layout. For example, assuming that A2:C7 contains the source data, let A11:A13 contain Week 1, Week 2, and Week 3, then let B10:I10 contain Phil, Brad, Guy, etc., then try...

B11, copied across and down:

=IF(COUNTIF(INDEX(\$A\$3:\$C\$7,0,MATCH(\$A11,\$A\$2:\$C\$2,0)),"?*"),IF(ISNUMBER(MATCH(B\$10,INDEX(\$A\$3:\$C\$7,0,MATCH(\$A11,\$A\$2:\$C\$2,0)),0)),"P","A"),"")

Adjust the ranges accordingly. Is this acceptable?

#### Jeffrey Smith

##### Well-known Member

Book1.xls
ABCDEFGHI
1
2Week1Week2Week3Week1Week2Week3
3PhilTomPhilPPP
5GuyGuyGuyPPP
6JimJimJimPPP
7TonyDaveTonyPPP
8DougAAA
9TomAAA
Sheet1

In the example above the cell G3 should be

Code:
``=IF(COUNTIF(\$A3:\$A100,\$F3)>0,"P","A")``

I rearranged the cells a bit. It's easy to copy the formula to the other cells.

Jeff

#### Jeffrey Smith

##### Well-known Member
Book1.xls
ABCDEFGHI
1
2Week1Week2Week3Week1Week2Week3
3PhilTomPhilPAA
5GuyGuyGuyPPA
6JimJimJimPPA
7TonyDaveTonyPAA
8DougAPA
9TomAPA
10
Sheet1

Sorry I got that wrong. shoulda checked things a bit

Cell G3 should be

Code:
``=IF(COUNTIF(A\$3:A\$100,\$F3)>0,"P","A")``

Jeff

#### zendog1960

##### Active Member

Jeff's formula worked to a degree but it doesn't take into account that during the course of the function (34 Weeks long), there will be weeks that are not yet attended. This needs to be accounted for so other data can be used based expressly on the number of weeks that have been attended only.

The three conditions are:

A = Absent - the function happened but the member was not present
P = Present - the funtion happened and the member was present
x = Future date - Future dates of the function that have not yet happened therefore 0 values can not be use because it will mess with other calculations.

Is there a way, without creating a new layout (mine is created and populated with 6 weeks of data so re-layouts are not an option), to use the three conditions via a formula that would change the "A"s in Jeffs example above for week 3 to blanks?

This would be a tremendous help? I am pulling my hair out over here trying to figure this out, wait, I don't have any hair!

Thanks for all your help thus far. I know we are close just need that last kick!

#### Bill H

##### New Member
Since changing the layout isn't an option, you might try...

in H2:

Code:
``=IF(COUNTA(\$A\$3:\$A\$7)=0,"",IF(COUNTIF(\$A\$3:\$A\$7,\$G\$1)>0,"P",IF(COUNTIF(\$A\$3:\$A\$7,\$G\$1)=0,"A")))``

You'll need to adjust the range/cell references to attain the desired results for the other attendees.

#### Domenic

##### MrExcel MVP
Here's another way...

H2, copied down and to Column J:

=IF(MOD(ROW()-ROW(H\$2),4)<>3,IF(COUNTA(INDEX(\$A\$3:\$C\$7,0,MATCH(G2,\$A\$2:\$C\$2,0))),IF(ISNUMBER(MATCH(INDEX(G\$1:G\$16,(INT(ROWS(H\$2:H2)/4)+1)*4-4+1),INDEX(\$A\$3:\$C\$7,0,MATCH(G2,\$A\$2:\$C\$2,0)),0)),"P","A"),""),"")

Hope this helps!

#### zendog1960

##### Active Member
Domenic, I am not sure why you are using the 'MOD' and "ROW' functions here. I am still learning Excel and all it's functions. Can you explain why the use of these two functions and why they might be better that say the formula that Bill H suggested. I have tried looking it up in help but we all know, the help doesn't always explain it in terms anyone except the programmers can understand.

I look forward to the explaination!

Replies
8
Views
656
Replies
1
Views
475
Replies
7
Views
204
Replies
1
Views
226
Replies
5
Views
95

1,136,503
Messages
5,676,230
Members
419,615
Latest member
jda2000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back