Log In
Register
Home
Active Member
March 4, 2021
Offline
Hello dear all,
Can someone please help me with this question?
On my excel file, I want to calculate all and only the percentages, like 0,00%, 5,00%, 50,00% and 100,00%, and not counting the other data like text or numbers.
The columns I need to be included in the average they could change depending on whether they have a % sign in them.
Please see my file attached.
Regards,
Thanks for your help
Carlos Sousa
VIP
Trusted Members
December 7, 2016
Offline
Hello Carlos,
Sorry, no file attached.
Br,
Anders
New Member
March 4, 2021
Offline
hey
try this
1. To find the position of a substring in a text string, use the SEARCH function.
2. Add the ISNUMBER function. The ISNUMBER function returns TRUE if a cell contains a number, and FALSE if not.
3. You can also check if a cell contains specific text, without displaying the substring. Make sure to enclose the substring in double quotation marks.
4. To perform a case-sensitive search, replace the SEARCH function with the FIND function.
5. Add the IF function. The formula below (case-insensitive) returns "Found" if a cell contains specific text, and "Not Found" if not.
6. You can also use IF and COUNTIF in Excel to check if a cell contains specific text. However, the COUNTIF function is always case-insensitive.
VIP
Trusted Members
June 25, 2016
Offline
Hi Carlos
You could try placing a helper column to check the format of each cell.
Example, in column B enter =CELL("format",A1)
It will return P0 if it is formatted as %
You can then use AVERAGEIFS() to calculate column A based on the format shown in column B (i.e. P0)
Hope this helps.
Sunny
Active Member
March 4, 2021
Offline
Dear all,
Can you take a look on the excel file, I try all this info, but still doesn't work or maybe I'm not doing well.
VIP
Trusted Members
June 25, 2016
Offline
Which cell is your expected answer? How did you derive at the answer?
Which columns are we expected to check against?
It it is not clear.
Active Member
March 4, 2021
Offline
The cell is;
AG 7
AG 8
AG 9
And so on
Trusted Members
Moderators
November 1, 2018
Offline
It would be really easy if you could add an identifier to your column headers - eg use "Supports mounted %" as the header. You can then use:
=SUMIF($A$6:$AF$6,"*%",A7:AF7)
and fill down.
1 Guest(s)
