How to calculate in a cell the sum of just the nominators of a column of fractions?
Not sure what you mean by nominators. There’s numerator (the top part) and denominator (the bottom part). Assuming you mean numerators it’s also unclear if you really mean only the numerators of fractions or also the whole numbers, so is 1 2/7 to calculate as 9, or 2?
Please clarify by editing your question to provide details (not as answer).
Yes sorry i was thinking denominators down, nominators up, i meant numerators. I mean only the numerators, these fractions do not have a number in front. But i don’t understand your example, 1 and 2/7 is about 1,2857. Yes?
The example 1 2/7 is 1+2/7 or 9/7. That’s what you get with the number format # ?/7 for the value 1.28571428571429
hallo
for Example you want to sum from D4:D19:
=SUM(LEFT(TEXT(D4:D19;"???/???");FIND("/";TEXT(D4:D19;"???/???"))-1)*1)
enter as Matrix- (Array?)-formula with strg+shift+enter
Hello,
I have tried the above formula with the relevant cells in my sheet but what i get is “#VALUE!” in the cell.
as i have written, you need to enter strg+shift+enter respectivly with enabled x matrix-option in Formulawizard
Sorry, i don’t understand.
When do i need to press Ctrl/Strg+Shift+Enter?
And what is the “x matrix option” i don’t know it.
I know how to choose a cell and press the “Function Wizard” button which brings up the Table of formulas. This table has two tabs, “Function” and “Structure”. Can we take it from there?
You either input the formula in a cell and close with Shift+Ctrl+Enter instead of just Enter, or when using the Function Wizard check the Matrix checkbox. Both make the formula an array formula, which is essential here.
However, calculating with text is ill advised and may lead to #VALUE! error (depends on settings), furthermore the number format ???/??? used in TEXT may lead to any mix of denominators, you certainly don’t want to sum numerators for such a mix. More correct would be
=SUM(VALUE(LEFT(TEXT(D4:D19;"?/7");FIND("/";TEXT(D4:D19;"?/7"))-1)))
Replace the 7 denominators with the one you actually want to use.
Then again, simply using this array formula would do the same
=SUM(ROUND(D4:D15*7))
(close with Shift+Ctrl+Enter).
Hello erAck and thanks for the reply. I did not manage to do what i want with the two formulas you have provided, i am using the array option and the exact formulas, both of them. I don’t understand why you are referring to denominators, i am not looking for a fraction. I am looking for a way to simply count the numerators of a column of fractions which have different denominators. Here is a screenshot of the very simple thing i want to do:
link text
We have three fractions which show successful attempts at different things. The numerator is the number of successful attempts and the denominator the total attempts. The number 16 is the sum of numerators, calculated by mind and simply inserted manually. Below, is the cell where i tried the formula. So i am just looking for the formula to sum all successful attempts which would be the sum of all the column’s numerators only.
I guess your fractions are simply Text ??
=SUM(LEFT(D2:D4;FIND("/";D2:D4)-1)*1)
AGAIN: HOLD CTR+SHIFT AND HIT ENTER
I don’t understand why you are referring to denominators
Because summing numerators of fractions with different denominators doesn’t make sense, but if that is your wish… ¯\(ツ)/¯
@karolus: please do not advise to calculate with text, I already mentioned above. It depends on the detailed string conversion settings and may or may not work. Use explicit VALUE() or NUMBERVALUE() to convert text to numeric (the *1 is also unnecessary then, it’s a workaround from Excel to force conversion but still depends on locale and is ill-advised for Calc).
=SUM(VALUE(LEFT(D2:D4;FIND("/";D2:D4)-1)))
Again entered as array formula with Shift+Ctrl+Enter.
Note this formula (and the original one) works only if the D2:D4 cells’ contents are all Text and not numeric formatted as fractions nor empty, in which both cases it produces a #VALUE! error because FIND("/";…) does not find anything.
From the screenshot provided by @TF8009 my guess is there is actually Text in D2:D4 because 9/9 in D3 is only possible as Value-representation with fixed 9 as denominator. (ok, exept the OP obfuscates us with misused DateFormat)
“my guess is there is actually Text in D2:D4”
No, it is fraction format, except the 9/9 which i opened a different question here about, which turned into 1 so i used the format of a fixed denominator (9 in this case) because i want to preserve the fraction format (it means success 9 times out of 9).
“Because summing numerators of fractions with different denominators doesn’t make sense, but if that is your wish… ¯_(ツ)_/¯”
I understand why it may seem to not make much sense, but in fact it has a specific logic to it, let me explain, you will agree it makes perfect sense for the reason it serves. The fractions are successful trades of different types. The numerator is the number of successes and the denominator the total number of trades. The sum will just be the total number of all types successful trades, therefore the sum of numerators. The reason i need the successes is because i want to use this sum to divide the total winnings which will be a currency number in a different cell, so that i can get an average winnings/per successful trade in a third cell.
Of course it could be the sum of all successful/total trades as a fraction, but then i would have to find a formula that divides the winnings (number) with just the numerator of a fraction in a cell. Possible?
I’d rather use two separate columns for successful trades and number of trades. That way you can calculate any sum and ratio you want with easy formulas.