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?
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
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]
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]
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
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]
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
cheers Humbug. Managed to meet up with my brother and used the MAX ROUND formula and worked a treat.
thanks all