Hello Community!
1. In pages I have created several tables with calculations (sum) of prices and discount at the end . In the last table I just want to add the SUM of each above table but the calculation is wrong see image below..
2. Also when I delete an entire table the "last" SUM table gives an error since it can't find the "deleted table". Is there any way to "ignore" the deleted table and sum the rest..
Thank you in advance
Yorgos’s MacBook Pro 15"
The SUM function is defined using comma separators, not semi-colons as you have shown.
You would have to wrap each element of the SUM function with an IFERROR, so that when a reference (e.g. table) is removed (e.g. #REF!), a 0 is substituted for that error in the SUM arguments.
So here, in a fourth table, I am summing a single cell from the first three tables. I have only encapsulated table 1's reference in an IFERROR function as an example, but all table references would receive this treatment to be thorough. The IFERROR function detects that Table 1 is present, so its value is provided to the SUM function.
and here, I have removed Table 1, and with the following formula changed, showing that it detected Table 1 is missing and causes a #REF! error. A zero is then provided to the SUM function in place of the missing Table 1 data, and a final total of Tables 2 and 3 is shown for the cell hosting this formula.
The SUM function is defined using comma separators, not semi-colons as you have shown.
You would have to wrap each element of the SUM function with an IFERROR, so that when a reference (e.g. table) is removed (e.g. #REF!), a 0 is substituted for that error in the SUM arguments.
So here, in a fourth table, I am summing a single cell from the first three tables. I have only encapsulated table 1's reference in an IFERROR function as an example, but all table references would receive this treatment to be thorough. The IFERROR function detects that Table 1 is present, so its value is provided to the SUM function.
and here, I have removed Table 1, and with the following formula changed, showing that it detected Table 1 is missing and causes a #REF! error. A zero is then provided to the SUM function in place of the missing Table 1 data, and a final total of Tables 2 and 3 is shown for the cell hosting this formula.
Hi Yioryos,
You are getting strange results because you have an error in the calculation of the discounted amount.
For simplicity, suppose the amount in C5 is 100.
Here's what your formula tells Numbers to do:
Get the value in C5 (100), multiply that value by the percentage in B6 (10% or 0.10), then subtract the amount in C5 (100) from the result of the multiplication.
(100*0.10)-100
solving the multiplication:
(10)-100
solving the subtraction:
10-100 = -90
Your formula should read:
C5 - (C5*B6)
Because the order of operations rules have multiplication and division completed before addition and subtraction, the parentheses wrapping C5*B6 are not necessary, and can be removed:
C5-C5*B6
Regards,
Barry
As I mentioned, each table reference in your final formula must be wrapped in an IFERROR just as I did in my example. In the U.S. the SUM function is defined with commas, and introducing a semi-colon is an error here.
I guess I am curious why in your preceding examples, you were summing the contents of C10 in each table, and in your formula above, you reference cell C7, and not C10, where the results of your discount calculation would be located?
I found that my nymber from the discount formula gives me a negative number and thats why in the last SUM table substract
the total and not add everything.. Any idea of how can I make this possitive? Maybe I need another calculation?
I think I found it.. I just multiple the resukt with -1 and gave me the possitive number..
that worked great!
The only issue now is that when I have 2 tables and in the one of them I have lets say a sum of numbers and in the other
I have a SUM and a dicount of 20% then when I add these two resuts it returns me the wrong SUM check image below..
PS in Europe the SUM of tables or cells is seperated by ;
Oh thanks that helped!
But what if I want to delete 2-3 tables? how can I add this rule inside "IFERROR" fuction?
Also SUM does not work with commas only with semicolons.. as by default..
Hi Yioryos,
Yioryos wrote:
I think I found it.. I just multiple the resukt with -1 and gave me the possitive number..
Or, 😉
Regards,
Ian.
Thank you everyone it seems that there are a couple of different solutions that return the same result..
Thank you all and kind regards!
Hi Yioryos,
Great minds think alike!
C5*B6 is the same as B6*C5
Regards,
Ian.
and here is the discount calculation
Calculations & tables in Pages
