CODEWITHSUNDEEP
excel
user4227915
user4227915

Reputation:

Excel - Formula to multiply time and sum up all them

Illustrative: enter image description here

I need to:

I've got this:

=((E3*10)+(F3*15)+(G3*20))/60

But it displays a fractional number or zero when there is nothing on the columns.

How can I do to display the result rounded up when needed and hide it when it is zero?

Thanks in advance.

Upvotes: 0

Views: 123

Answers (3)

Lobsterpants
Lobsterpants

Reputation: 1258

Your formula doesn't quite match up to the image. I believe it should be:

=((D3*10) + (E3 * 15) + (F3 * 20))/60

To simply hide it you could use an if statement:

=IF(((D3*10) + (E3 * 15) + (F3 * 20))/60 = 0,"",((D3*10) + (E3 * 15) + (F3 * 20))/60)

Though personally I would add an extra hidden column (D) in and put the formula with the rounding in there as then then is only one place to change it 9should you need to at a later date):

=ROUND(((D3*10)+(E3*15)+(FG3*20))/60,0)

with column c containing the if statement:

=IF(D3 = 0,"",D3)

Alternatively - to avoid the if statement

You could just hide all zero values on the sheet:

  • Click the File tab, click Options, and then click the Advanced category.
  • Under Display options for this worksheet, select a worksheet, and then deselect the Show a zero in cells that have zero value check box

.

Upvotes: 2

James
James

Reputation: 36756

Since you want this rounded to the nearest whole hour, but blank if 0, use:

=IF(ROUND(((E3*10)+(F3*15)+(G3*20))/60,0)>0,ROUND(((E3*10)+(F3*15)+(G3*20))/60,0),"")

Upvotes: 1

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

=IF(<<yoursum>> = 0, "", ROUNDUP( <<yoursum>>, 0))

Upvotes: 1

Related Questions