Calculate Percentage without Empty Fields

[Pop_Access]

Hi All,

How I can calculate the percentage for a numeric field in report with the following conditions:

1- field is not empty (Contains value).
2- if the field value >= 4.5

For example:-

Mark

10

0

Afgf

7

3

the percentage should be (2 (10 and 7 >=4.5)) / (4 (not 5)) = 50%

 

[theDBguy]

Hi. What is "Afgf?" If it's a numeric field, why does it contain a text value?

 

[Eugene-LS]

May be IIf function ...
=IIF([Your field name]>=4.5; [Your field name]/4; 0)

 

[Pop_Access]

Hi. What is "Afgf?" If it's a numeric field, why does it contain a text value?

sorry; this field should be empty, but to keep the row, I wrote the text "Afgf" with white color

 

[Pop_Access]

May be IIf function ...
=IIF([Your field name]>=4.5; [Your field name]/4; 0)

Thank you, but the number of fields is not fixed

 

[Eugene-LS]

but the number of fields is not fixed

I think this is solvable, besides you can use IIF inside another IIF ...
BTW - The longest IIF expression I've seen:
Analysis_Result: IIf([COMPLETION DATE] IS NOT NULL,IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C in App4.",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))),IIf([tbl_CMS_WO_2_Full].[STATUS]<=10 OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "98" OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "99","WO not yet implemented",IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C Test Date in App4. Additional investigation",IIf([COMPLETION DATE] IS NULL AND [COMPLETED/COMPLETION DATE] IS NULL,"",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))))))
believe me - I didn't write it

 

[theDBguy]

sorry; this field should be empty, but to keep the row, I wrote the text "Afgf" with white color

Okay, thanks for the clarification. You could try using DCount(). For example.

DCount("*", "TableName", "FieldName>=4.5") / DCount("*", "TableName", "FieldName Is Not Null")

 

[arnelgp]

you create 3 queries:
1st query (query1):

SELECT Sum(yourTable.Mark) AS TotalMarks
FROM yourTable
HAVING (((Sum(yourTable.Mark))>4.5));

2nd query (query2)

SELECT Count(yourTable.Mark) AS Cnt
FROM yourTable;


the Final query (average query):

SELECT DLookUp("TotalMarks","Query1")/DLookUp("Cnt","Query2") AS Average
FROM yourTable
GROUP BY DLookUp("TotalMarks","Query1")/DLookUp("Cnt","Query2");

 

[SHANEMAC51]

I think this is solvable, besides you can use IIF inside another IIF ...
BTW - The longest IIF expression I've seen:

maximum of 14 nested

 

[Eugene-LS]

maximum of 14 nested

Thanks for reminding (me)!

 

[Pop_Access]

I think this is solvable, besides you can use IIF inside another IIF ...
BTW - The longest IIF expression I've seen:
Analysis_Result: IIf([COMPLETION DATE] IS NOT NULL,IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C in App4.",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))),IIf([tbl_CMS_WO_2_Full].[STATUS]<=10 OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "98" OR [tbl_CMS_WO_2_Full].[STATUS] LIKE "99","WO not yet implemented",IIf([SCI52 ELECTRICAL IMPACT] IS NOT NULL,"SCI52E only.",IIf([MaxOfMaxOfTest DATE] IS NULL,"No I&C Test Date in App4. Additional investigation",IIf([COMPLETION DATE] IS NULL AND [COMPLETED/COMPLETION DATE] IS NULL,"",IIf(DateDiff("d",[Implementation_Date],[SCI_5253I])<0,"No retest after implementation",IIf([Implementation_Date]<=[MaxOfDate_of_Test],[LastOfCDS],IIf([SCI_5253I]>[MaxOfDate_of_Test],"Last test is after last CDS","Last test is after last CDS"))))))))
believe me - I didn't write it

I really believe you

 

[Pop_Access]

I would like to share with you what I did, I wrote the following code in unbound textbox in the report footer:-

=FormatNumber(Sum(Abs([Evaluation]>=4.5))/Count(*)*100,2)+" %"

but unfortunately, I can not exclude the empty records if any in [Evaluation] field as I want.

 

[arnelgp]

replace Count(*) with Count([Evaluation])

 

[Pop_Access]

replace Count(*) with Count([Evaluation])

Great job; It was very easy

 

[Pat Hartman]

If your amounts default to null, the Avg() function works correctly since it ignores null. So if you have three rows with the following values,
3, null, 3 --- the average is 3
If the default is 0, then the average for
3, 0, 3 --- is 2

So,
Select Avg(score) as avgscore
From Your table;

Will always be correct if your default is null. If your default is 0, I'm not sure how you would know which 0 is an earned 0 vs a not entered yet value.

If you are averaging named columns in a table - you need to take off your Excel hat and normalize the table. There are no functions that will perform this calculation for you in a relational database because the structure violates first normal form. You can do it, but you need to write it yourself and you need to figure out what the denominator is based on the number of null values.

 

[Pop_Access]

If your amounts default to null, the Avg() function works correctly since it ignores null. So if you have three rows with the following values,
3, null, 3 --- the average is 3
If the default is 0, then the average for
3, 0, 3 --- is 2

So,
Select Avg(score) as avgscore
From Your table;

Will always be correct if your default is null. If your default is 0, I'm not sure how you would know which 0 is an earned 0 vs a not entered yet value.

If you are averaging named columns in a table - you need to take off your Excel hat and normalize the table. There are no functions that will perform this calculation for you in a relational database because the structure violates first normal form. You can do it, but you need to write it yourself and you need to figure out what the denominator is based on the number of null values.

Thank you for your Idea. the problem has been solved by using the following formula:-

=FormatNumber(Sum(Abs([Evaluation]>=4.5))/Count(Evaluation)*100,2)+" %"

Thanks for All.

 

[Pat Hartman]

Count(fieldname) counts only rows with non-null values in fieldname. Count(*) counts all rows in recordset which seems to be what you want.

[Evaluation]>=4.5 returns either 0 or -1 so that is what you are summing.

+ is the arithmetic operator. It can, under certain circumstances, be used as a concatenation operator but it has different properties. Unless you understand them and are specifically trying to take advantage of how + works with nulls when you are concatenating (which you are not), stick to the mundane &.

Not sure why you are multiplying by 100.2 though

 

[Pop_Access]

Count(fieldname) counts only rows with non-null values in fieldname. Count(*) counts all rows in recordset which seems to be what you want.

[Evaluation]>=4.5 returns either 0 or -1 so that is what you are summing.

+ is the arithmetic operator. It can, under certain circumstances, be used as a concatenation operator but it has different properties. Unless you understand them and are specifically trying to take advantage of how + works with nulls when you are concatenating (which you are not), stick to the mundane &.

Not sure why you are multiplying by 100.2 though

Thank you for your advice regarding using "&" instead of "+"
I multiply by 100 to display the result before the comma, then I used ",2" to display only two fractions.