mads
|
| posted on 16/3/10 at 10:12 PM |
|
|
excel function and conditional formatting help please
Dear all,
I cant quite figure out how to sort a solution for a query my brother put to me....
He wants to format the cells of a spreadsheet so they do the following:
- if cell value is less than 2, then the value defaults to 2
- the cell value needs to round up or down to the nearest whole integer
the cell values are calculated from information within the spreadsheet.
anyone have any suggestions?
|
|
|
|
|
Miks15
|
| posted on 16/3/10 at 10:22 PM |
|
|
If you format the cell, instead of general, change it to number, and then set it to 0 decimal places, it will then display it as an interger. Im not
sure about the minimum of 2 though sorry
|
|
|
Humbug
|
| posted on 16/3/10 at 10:26 PM |
|
|
What you are talking about is not really conditional formatting as described in Excel.
Conditional formatting is something like "if the value of cell X > 5, then shade it in red"
To make the cell contain the value you want, you need to wrap whatever the formula is to calculate from other infomation within the spreadsheet with
formula to manipulate, e.g.
=if(XXXX<2,2,round(XXXX,0))
Where XXX is whatever the fomula you've already got is. What it does is first check if the calue is less than 2, in which case it makes the
result = 2; otherwise rounds to the nearest value to 0 decimal place.
A bit difficult to explain without you existing formula, but hopefully you get the drift.
Simon
EDIT: if you only want it to display as you described, I think Number formatting would only do the rounding; not sure how you could use it to set a
minimum value.
[Edited on 16.03.2010 by Humbug]
|
|
|
RoadkillUK
|
| posted on 16/3/10 at 10:26 PM |
|
|
I'm a noob with Excel, only use it's basics but here goes.
Assuming Cell A1 holds the original number then use this in B1 ...
=IF(A1<2,2,A1)
and set the 'B' Column to 0 decimal places (if you follow) 
Edited to add that 2.0 to 2.49 will result in a 2 whereas 2.5 to 2.99 will result in a 3
[Edited on 16/3/10 by RoadkillUK]
Roadkill - Lee
www.bradford7.co.uk
Latest Picture (14 Sept 2014)
|
|
|
rachaeljf
|
| posted on 16/3/10 at 11:36 PM |
|
|
Try:
=MAX(ROUND(formula,0),2)
This will round whatever formula you wish to the nearest integer, and it will never be less than 2.
You can quick-format the cell(s) to your desired number of decimal places by using the toolbar button that looks something like
"->.00"
Cheers R
|
|
|
mads
|
| posted on 17/3/10 at 12:49 PM |
|
|
quote:
Originally posted by Humbug
What you are talking about is not really conditional formatting as described in Excel.
Conditional formatting is something like "if the value of cell X > 5, then shade it in red"
To make the cell contain the value you want, you need to wrap whatever the formula is to calculate from other infomation within the spreadsheet with
formula to manipulate, e.g.
=if(XXXX<2,2,round(XXXX,0))
Where XXX is whatever the fomula you've already got is. What it does is first check if the calue is less than 2, in which case it makes the
result = 2; otherwise rounds to the nearest value to 0 decimal place.
A bit difficult to explain without you existing formula, but hopefully you get the drift.
Simon
EDIT: if you only want it to display as you described, I think Number formatting would only do the rounding; not sure how you could use it to set a
minimum value.
[Edited on 16.03.2010 by Humbug]
Humbug - tried this. Works great for all values under 2 but not for any over 2.
The formula he is using that gives the cell value: =(F7/50)/3
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
Humbug
|
| posted on 17/3/10 at 01:07 PM |
|
|
quote:
Originally posted by mads
quote:
Originally posted by Humbug
What you are talking about is not really conditional formatting as described in Excel.
Conditional formatting is something like "if the value of cell X > 5, then shade it in red"
To make the cell contain the value you want, you need to wrap whatever the formula is to calculate from other infomation within the spreadsheet with
formula to manipulate, e.g.
=if(XXXX<2,2,round(XXXX,0))
Where XXX is whatever the fomula you've already got is. What it does is first check if the calue is less than 2, in which case it makes the
result = 2; otherwise rounds to the nearest value to 0 decimal place.
A bit difficult to explain without you existing formula, but hopefully you get the drift.
Simon
EDIT: if you only want it to display as you described, I think Number formatting would only do the rounding; not sure how you could use it to set a
minimum value.
[Edited on 16.03.2010 by Humbug]
Humbug - tried this. Works great for all values under 2 but not for any over 2.
The formula he is using that gives the cell value: =(F7/50)/3
I just tried it again, substituting "(F7/50)/3" for "XXXX" in my original formula. It does work, but the value of F7 has to be
375 or more in order for (F7/50)/3 to result in a value of 2.5 or more and therefore trigger the rounding function to round up.
ETA: =MAX(ROUND((F7/50)/3,0),2) as described above also works
[Edited on 17.03.2010 by Humbug]
|
|
|
mads
|
| posted on 28/3/10 at 06:27 PM |
|
|
cheers Humbug. Managed to meet up with my brother and used the MAX ROUND formula and worked a treat.
thanks all
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
