Forum home » Delegate support and help forum » Microsoft Excel Training and help » Advanced formula's
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Advanced formula's
Resolved · Urgent Priority · Version 2007
Reply Wed 29 Oct 2008, 14:13Delegate Charnel said...
Charnel has attended:
Excel Advanced course
Advanced formula's
If i have a number of columns in a sheet say a column for each month ie 12 columns.
What i'd like to know is how to sum only the columns which contain numbers in it for eg. column 1-10 have numbers in it and the last two columns have formulas but no numbers, how to i include a total column that automatically adds the columns with numbers and updated automatically if for instance a number is included in column 11?
Thanks
For upcoming training course dates see: Pricing & availability
RE: Advanced formula's
Hi Charnel
Thanks for your question. To make sure I understand the problem can I check what happens when you put a sum function in column 13 that sums the values and the cells with formulas? Usually Excel's sum function will ignore the contents of cells with no value or non relevant text - if the contents of these cells changes to contain values that could be summed Excel automatically includes them in the result.
Let me know how you get on and we'll try to help further.
Kind regards,
Andrew
Reply Wed 29 Oct 2008, 15:23Delegate Charnel said...
RE: Advanced formula's
Hi Andrew
So basically what i have is that in month 11 and 12 i have formula's which calculate a EURO amount based on the exchange rate input into another cell so in 11 and 12 it shows #DIV/0!.
Therefore in my total column it also shows #DIV/0!. And what i'd like it to do is add all columns as the #DIV/0! is cleared since the exchange rate in inputted and the amount is calculated.
Hope you can help
thanks
RE: Advanced formula's
Good Afternoon Charnel,
An option might be to fix the #DIV/0 using an If formula.
E.G if existing formula is =D14/D15 then new formula would be =IF(E15>0,E14/E15,). This checks if the dividing cell is greater than zero, if it is then it continues with the division, if not it returns no value.
Hope this assists
Regards
Tristan
|