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
Hello Carlos,
Sorry, no file attached.
Br,
Anders
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.
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
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.
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.
The cell is;
AG 7
AG 8
AG 9
And so on
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.
Dear Veloria,
I did exactly as you explain above, but still have an error!
Could you please send the excel list with that formula?
Thanks for your help
Here you are. I just added "%" to each header.