Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
why is excel rounding my totals down | Excel Discussion (Misc queries) | |||
Rounding and cell totals | Excel Worksheet Functions | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |