Pivot Table challenge

[jetta2]

Two days working on this and no solution. Please help! I have data
displayed in a pivot table that looks like this:

Practice Pt ID Data (tests) Total *Score
1234 103 Count of HbA1c 4 2
Count of eye exam 1 1
Count of lipids 1 1
Total score 4
234 Count of HbA1c 3 2
Count of eye exam 1 1
Count of lipids 3 2
Total score 5

I need to add a column that re-counts the data in each of the test fields to
produce a number that represents a minimum requirement, then total that new
column to produce a total score. An example of the formula I am using in
Access and would like to use to convert HbA1c in Excel is =iif(count([HbA1c])
<= 2, count([HbA1c]), iif(count([HbA1c]) >2, 2, '*'))
I am a novice, and this may well be beyond my knowledge base, but ANY
guidance would be greatly appreciated!

 

[JE McGimpsey]

I need to add a column that re-counts the data in each of the test fields to
produce a number that represents a minimum requirement, then total that new
column to produce a total score. An example of the formula I am using in
Access and would like to use to convert HbA1c in Excel is =iif(count([HbA1c])
<= 2, count([HbA1c]), iif(count([HbA1c]) >2, 2, '*'))
I am a novice, and this may well be beyond my knowledge base, but ANY
guidance would be greatly appreciated!

It's hard for me to visualize exactly what you're asking for - what
exactly are you trying to recount?


Given your mention of Access, I'd guess you're not using MacXL (which
this newsgroup is dedicated to), so you may be able to reach more
potential answers in one of the WinXL-oriented newsgroups, like
microsoft.public.excel.

 

[CyberTaz]

Yeah, I couldn't determine exactly what the OP wants to have done in XL
either, but there are a few "Access Only" considerations even if it's just a
matter of adapting the expression:

1- The IIF function isn't supported in XL, so a regular IF should replace
it,

2- The [HbA1c] syntax used in Access to refer to a field/control name needs
to be replaced by a cell reference for the location of the value, and

3- If I'm even close to understanding what is being called for there is no
need for the COUNT() fx to be included in the XL flavor of the expression.

I also doubt whether a nested IF is even necessary... it looks like the
truepart of <=2 and the falsepart of >2 would be redundant. If the value
being tested is stored in cell B25 for example, I'm seeing something as
simple as:

=IF(B25<=2,B25,2)

But I may be way off base... it's just interesting mental exercise for a
text-rooted sot such as myself :0)

--
Regards |:>)
Bob Jones
[MVP] Office:Mac

I need to add a column that re-counts the data in each of the test fields
to
produce a number that represents a minimum requirement, then total that
new
column to produce a total score. An example of the formula I am using in
Access and would like to use to convert HbA1c in Excel is
=iif(count([HbA1c])
<= 2, count([HbA1c]), iif(count([HbA1c]) >2, 2, '*'))
I am a novice, and this may well be beyond my knowledge base, but ANY
guidance would be greatly appreciated!

It's hard for me to visualize exactly what you're asking for - what
exactly are you trying to recount?


Given your mention of Access, I'd guess you're not using MacXL (which
this newsgroup is dedicated to), so you may be able to reach more
potential answers in one of the WinXL-oriented newsgroups, like
microsoft.public.excel.