ExcelBanter - Rounding up totals.

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Rounding up totals. (https://www.excelbanter.com/excel-discussion-misc-queries/167155-rounding-up-totals.html)

Del[_2_]

Rounding up totals.

Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
..
..
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

Mike H

Rounding up totals.

Hi,

With no empty cells in your range tray this entered with Ctrl+Shift+enter

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))

Mike

"Del" wrote:

Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

vezerid

Rounding up totals.

Use this *array* formula (commit with Shift+Ctrl+Enter)

=SUM(IF(F14:F18<500,50,F14:F18/10))

HTH
Kostis Vezerides

On Nov 23, 4:42 pm, Del wrote:
Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

Del[_2_]

Rounding up totals.

Hi Mike

Many thanks for your quick reply.

My fault, but there wont always be a value in every cell as they are sales
figures and will only be entered as required.

regards

"Mike H" wrote:

Hi,

With no empty cells in your range tray this entered with Ctrl+Shift+enter

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))

Mike

"Del" wrote:

Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

Mike H

Rounding up totals.

Theres almost certainly a better way but until then try this which now copes
with blanks

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))-(COUNTBLANK(F14:F63)*50)

Once again array entered.

Mike

"Del" wrote:

Hi Mike

Many thanks for your quick reply.

My fault, but there wont always be a value in every cell as they are sales
figures and will only be entered as required.

regards

"Mike H" wrote:

Hi,

With no empty cells in your range tray this entered with Ctrl+Shift+enter

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))

Mike

"Del" wrote:

Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

Del[_2_]

Rounding up totals.

Hi Mike

Many thanks.

That works fine. This is part of a larger formula, so hopefully I can get it
to work with that!

Again many thanks for your time.

regards

"Mike H" wrote:

Theres almost certainly a better way but until then try this which now copes
with blanks

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))-(COUNTBLANK(F14:F63)*50)

Once again array entered.

Mike

"Del" wrote:

Hi Mike

Many thanks for your quick reply.

My fault, but there wont always be a value in every cell as they are sales
figures and will only be entered as required.

regards

"Mike H" wrote:

Hi,

With no empty cells in your range tray this entered with Ctrl+Shift+enter

=SUM(IF(F14:F63*0.150,F14:F63*0.1,50))

Mike

"Del" wrote:

Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

Del[_2_]

Rounding up totals.

Hi Vezerid

Mike H just beat you! Many thanks for your time.

regards.

"vezerid" wrote:

Use this *array* formula (commit with Shift+Ctrl+Enter)

=SUM(IF(F14:F18<500,50,F14:F18/10))

HTH
Kostis Vezerides

On Nov 23, 4:42 pm, Del wrote:
Hi

I am trying to total (in cell F4) the percentage of a range of cells but
with a minimum value.

In cells F14:F63 I have the amounts:

F14 600
F15 500
F16 350
F17 700
F18 275
.
.
etc

I want to total 10% of each of the figures, but where the result is less
than 50, it should be rounded up to 50. So...

F14 600 = 60
F15 500 = 50
F16 350 = 50
F17 700 = 70
F18 275 = 50.

Have tried various methods but can't find a way to make it work. All help
greatly appreciated.

Regards

All times are GMT +1. The time now is 01:07 AM.