MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Hello all, I'm hoping that you can help.
I'm trying to construct a spreadsheet for school. The idea is that pupils can type in values for height and mass to then find out BMI. I am aware of the limits of BMI as an indicator of health. I am hoping to show this to pupils by inputting values from celebs. Thus Arnie is deemed to be obese.
What I would like to do is to find a way to format a cell so that if it reaches a certain value it triggers another cell to exhibit a certain word and a certain background.
Thus, when Arnie's BMI appears in cell F14, cell F15 turns red and displays the text 'Obese'.
Any and all help welcomed...
I've not got excel in front of me so I'm trying to remember but you would use a formula in the condition for the formatting of F15 which would be F14>x where x is the value at which your condition is met (the value of BMI you consider to be obese).
Displaying the text you'd normally do in the cell e.g. in cell F15: =IF(F14>x,"Obese","") but if you're set on doing it as a format I suppose you could have the text in the cell made invisible by the default format - e.g. Obese in black on a black background, then the format turns the background red, revealing the word..
build a nested If statement in cell F15 so it contains something like If((F14>x, "Obese") Else (F14>y, "underweight) etc
then conditional format F15, again you may need to plug in the value range for each RAG type rather than looking for the word result, depending on which version of excel you have
to make it easier I would have the user type their height into F12, their weight into F14 and F14 is the calculation, then you can do the if statment on the calculated result, if that makes sense, then they can see how everything works together
in cell F15 type:
=if(f14<18.5, "Underweight", if(f14<25, "Healthy", if(f14<30, "Overweight", "Obese")))
Then in F15 set the conditional formatting using the function description:
=f15="Obese"
etc
Thanks guys, I'll try it all out.
OP what you describe isn't really conditional formating as I understand it, strictly that's where say negative numbers appear in red, positive in blue for example. The if statements above will work and you can add a blank string """ for normal so that only "Obese" stands out - you can imbed the background and colours in the text I think by just formatting what you are typing in
The latest version of Office for Mac has a conditional formatting item in the ribbon. Which would appears to do what you require quite simply in conjunction with the IF function. (This is not a guarantee that the same functionality is available in older versions).
You need two things.
An IF statement to look at the number and provide a text response.
then put conditioanl formatting on it to change the formatting to match the words.
So Three cells
A1 type in height
B1 type in Weight
C1 = magic formula that determines BMI (I ahve no idea how you plan on that but
D1 will be the text output assessing that BMI
=if(C1<20, "Underweight", if(and(C1>=20, C1<25),"Healthy",if(and(C1>=25, C1<30),"Overweight",if(and(C1>=30, C1<50),"Yo Momma so fat",if(and(C1>=50,"you'll need a crane fat","No Number"))))
*#note all numbers are made up, i have no idea what a good BMI is
select this cell (or range of cells and put a condiitional format on that if Cell is equal to "Yo Momma so fat" it puts one type of formatting on. Then put another conditional formatting rule on the same cells for "Underweight". Repeat until all options covered.
Yayy, It lives!
Cheers guys, many thanks indeed. FWIW, much as I'd love to use 'Yo momma soooo fat..' I decided it wouldn't be politic to do so. I went for Keeebab-ahoy! instead.
You can do formula based conditional formatting in the latest excel, so you could do it without the formula in F15
Probably makes sense to do it that way though.