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/

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/

Actually I should have made this a bit clearer

How in Excel do you get it to put text in the same cell as the result of a
formula, i.e., it places the text straight after the calculation without you
having to put the text in the adjacent cell to the right. Thus the result
would be:

1015.5 hPa, or 76%, or 64 mm....and so on.

Also, when calculating the differences from the LTA, how can you get Excel
to put a '+' sign in front of the result to signify a positive anomaly.
________________
Nick G
Otter Valley, Devon
83 m amsl
http://www.ottervalley.co.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

In article ,
Brian Wakem writes:
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).



So long as there are no values 0 or greater than or equal to 2. (From
the way the question was posed, I assume that 0 or 1 is still wanted as
the answer in those cases.)
--
John Hall
"If a man will begin with certainties, he shall end in doubts;
but if he will be content to begin with doubts,
he shall end in certainties." Francis Bacon (1561-1626)