EXCEL: If column B contains a # between two numbers, and if column A is between certain #, then average their respective numbers in column C?
This SUMPRODUCT function will return 1559.495 as the average: =SUMPRODUCT(--(A:A>=10)*(A:A<=12)* (B:B>=90)*(B:B<=94)*(C:C))/ SUMPRODUCT(--(A:A>=10)*(A:A<=12)* (B:B>=90)*(B:B<=94)) Format the cell containing the formula to 2 decimal places. Edit: The SUMPRODUCT function is the equivalent of a built-in array function. Array functions are much more powerful formulas, entered with the key combination Control-Shift-Enter, instead of just 'Enter'. What this formula does is build an internal array of True/False values based on the values in column A and column B. When the array returns True/True for a value in column A and the value in the corresponding row in column B, it adds the corresponding value in column C to the accumulated sum. The use of the '*' between each element of the formula has a special function. It essentially tells Excel to require each element to be 'True' to tabulate values, not to actually 'multiply' values in this case. Of course, you can also use the '*'. '+','-', and '/' mathematical symbols with the SUMPRODUCT function to simply perform the math function indicated. So, for the six rows in your example, based on your criteria for each column, the array would return: True - True True - False False - False False - True False - False True - True Thus it would sum the values in column C for the first and last entries in your table. That would be 3118.99. The second part of the formula then counts the number of entries in your table where the criteria for column A and column B is True. So that total would be '2', the first and last entries. That value is then divided into 3118.99, resulting in the average of 1559.495. For me, the SUMPRODUCT function is easier to use, and more flexible, than the COUNTIFS function. You would be surprised how many times this function is applicable to data evaluation in Excel.
How do I keep Excel from turning numbers in parentheses into negative numbers?
In much of the finance world, negative numbers are put in parentheses instead of prefixed with a negative sign. I suppose it's easier for them to distinguish at a glance. You can change the formatting for those cells so it applies parentheses instead of negative signs (or even both). If the numbers aren't actually negative, aren't part of a formula (where parentheses are used for functions and changing order of operations), and don't need to be calculated, use the Text format or prefix with an apostrophe as Lee-Ma suggests.
Excel formula to get a number value out of a field that contains text?
I have a column of fields (some examples below): TTT,4,RRR TJG,4*8,LLL,10 PLT,LAN,4 LLL,TJG LLL I am trying get the number value(s) only after commas. If there are two number values showing after multiple commas, would like the output to have a comma ex. 4,4. If one, just the number ex. 4 If no number in the field, return "". Also trying to make sure to avoid numbers that show after an asterisk. I have done research and don't know if this is possible. Thank you so much for your help!