Help with some formulas

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
453
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
4BradDougP = PresentWeek 3Week 3
5GuyGuy(Blank) = Future DateBradDoug
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.

please help!
 

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.

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
453
Sorry had a thread problem but the spreadsheet now shows. Please help if you can!
 

Jeffrey Smith

Well-known Member
Joined
Feb 11, 2005
Messages
795
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
Joined
Mar 10, 2004
Messages
19,697
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
Joined
Feb 11, 2005
Messages
795

ADVERTISEMENT

Book1.xls
ABCDEFGHI
1
2Week1Week2Week3Week1Week2Week3
3PhilTomPhilPPP
4BradDougBradPPP
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
Joined
Feb 11, 2005
Messages
795
Book1.xls
ABCDEFGHI
1
2Week1Week2Week3Week1Week2Week3
3PhilTomPhilPAA
4BradDougBradPAA
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
Joined
Sep 27, 2003
Messages
453

ADVERTISEMENT

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
Joined
Mar 23, 2002
Messages
21
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
Joined
Mar 10, 2004
Messages
19,697
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
Joined
Sep 27, 2003
Messages
453
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!
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top