Advanced formula's

Resolved · Urgent Priority · Version 2007

Wed 29 Oct 2008, 14:13 Delegate 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

Wed 29 Oct 2008, 14:52 Trainer Andrew said...

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

Wed 29 Oct 2008, 15:23 Delegate 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

Wed 29 Oct 2008, 16:42 Trainer Tristan said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Shared Conditional Formatting

In a shared workbook, conditional formats applied before the workbook was shared will continue to work; however you cannot modify the existing conditional formats or apply new ones.

View all Excel hints and tips