This is realy simple to most but I'm struggling with it.

I have a column in an excel spreadsheet with numbers as follows:

0.99
1.04
1.03
1.04
1.04

What I want to show on the next column is either '0' or '1' depending on
whether the number is below 1 (0) or above 1 (1), wht formula can I put
in the column to the right of those numbers to produce that variable?

Many thanks
--
Keith (Southend)
http://www.southendweather.net
e-mail: kreh at southendweather dot net

Keith (Southend) wrote:

What I want to show on the next column is either '0' or '1' depending on
whether the number is below 1 (0) or above 1 (1), wht formula can I put
in the column to the right of those numbers to produce that variable?

=IF(A11,0,1)

Checks whether A1 is less than 1, if it is it inserts the value after the
first comma, otherwise inserts the value after the second comma.
--
Steve Loft
Sanday, Orkney. 5m ASL. http://sanday.org.uk/weather
Free weather station softwa http://sandaysoft.com/
uk.sci.weather FAQs/glossary/etc: http://weatherfaqs.org.uk/

Keith (Southend) wrote:

This is realy simple to most but I'm struggling with it.

I have a column in an excel spreadsheet with numbers as follows:

0.99
1.04
1.03
1.04
1.04

What I want to show on the next column is either '0' or '1' depending on
whether the number is below 1 (0) or above 1 (1), wht formula can I put
in the column to the right of those numbers to produce that variable?

Many thanks


Never used excel but int(cell) should work (it does in open office calc).


--
Brian Wakem

On Sun, 16 Mar 2008 10:58:16 +0000, "Keith (Southend)"
wrote:

This is realy simple to most but I'm struggling with it.

I have a column in an excel spreadsheet with numbers as follows:

0.99
1.04
1.03
1.04
1.04

What I want to show on the next column is either '0' or '1' depending on
whether the number is below 1 (0) or above 1 (1), wht formula can I put
in the column to the right of those numbers to produce that variable?

Many thanks

Assuming the data is in column A, then something like

=IF(A11,0,1)


The if function works like this

IF(logical_test,value_if_true,value_if_false)

So for the example above it effectively says

If Cell A1 is less than 1, then make the value 0, otherwise make it 1

HTH

Noz

Steve Loft wrote:
Keith (Southend) wrote:

What I want to show on the next column is either '0' or '1' depending on
whether the number is below 1 (0) or above 1 (1), wht formula can I put
in the column to the right of those numbers to produce that variable?

=IF(A11,0,1)

Checks whether A1 is less than 1, if it is it inserts the value after the
first comma, otherwise inserts the value after the second comma.

Wow, that was quick :-)

That does the job perfectly.

Many thanks

--
Keith (Southend)
http://www.southendweather.net
e-mail: kreh at southendweather dot net

Steve

As you seem to be in the 'know' on Excel issues, I would like to get Excel
to calculate how much a given reading is below or above the long-term
average. Like this example:

Mean Min LTA
7.0 6.5 +0.5
5.0 6.5 -1.5

If you get my drift....cheers.
_______________
Nick G
Otter Valley, Devon
83 m amsl
http://www.ottervalley.co.uk

Nick Gardner wrote:
Steve

As you seem to be in the 'know' on Excel issues, I would like to get Excel
to calculate how much a given reading is below or above the long-term
average. Like this example:

Mean Min LTA
7.0 6.5 +0.5
5.0 6.5 -1.5

If you get my drift....cheers.
_______________
Nick G
Otter Valley, Devon
83 m amsl
http://www.ottervalley.co.uk



Easy !
Let A1 = 7.0
Let B1 = 6.5
In cell C1 the formula would be =A1-B1


--
Joe Egginton
Wolverhampton
175m asl

Cheers Joe, I don't think I was putting my brain into gear this morning when
I posted that as it now seems so obvious.

The next problem doesn't seem that obvious to sort out:

Rain LTA %
77.0 64.3 ??

Also:

Pressure LTA
1015.5 1018.2 +/- ??? hPa


What formula would you put into the cells to give the ????

Thanks
________________
Nick G
Otter Valley, Devon
83 m amsl
http://www.ottervalley.co.uk

Nick Gardner wrote:

Rain LTA %
77.0 64.3 ??

If rain is in A1 and LTA is in B1:

=A1*B1/100

Pressure LTA
1015.5 1018.2 +/- ??? hPa

This is just the same as your first question, isn't it?

=A1-B1
--
Steve Loft
Sanday, Orkney. 5m ASL. http://sanday.org.uk/weather
Free weather station softwa http://sandaysoft.com/
uk.sci.weather FAQs/glossary/etc: http://weatherfaqs.org.uk/

Steve Loft wrote:

Nick Gardner wrote:

Rain LTA %
77.0 64.3 ??

If rain is in A1 and LTA is in B1:

=A1*B1/100

Whoops, wrong way round - should be

=A1/B1*100

--
Steve Loft
Sanday, Orkney. 5m ASL. http://sanday.org.uk/weather
Free weather station softwa http://sandaysoft.com/
uk.sci.weather FAQs/glossary/etc: http://weatherfaqs.org.uk/