Looking for a macro or formula to use in excel.

  • Thread starter Thread starter Keith (Southend)G
  • Start date Start date
K

Keith (Southend)G

I wonder if someone could help me with this 'number crunching'
problem.

Basically, I'm trying to convert a temperature number back into a 5
digit number (part of meteorological synop code). My problem is there
are a number of variables to contend with which I'm struggling with.
Here is a series of temperatures (in °C) and how I would like excel to
return them:

0.0 - 10000
0.1 - 10001
5.3 - 10053
28.0 - 10280
28.4 - 10284
-0.5 - 11005
-1.0 - 11010 (Note the second 1 in the synop code denotes a minus)
-15.6 - 11156

I currently have a spreadsheet I use but know I will have a problem
when the temperature falls below zero '0'

Any help would be greatly appreciated as my programming/visual basic
language is sparce.

Many thanks

Keith (Southend)
http://www.southendweather.net
 
I wonder if someone could help me with this 'number crunching'
problem.

Basically, I'm trying to convert a temperature number back into a 5
digit number (part of meteorological synop code). My problem is there
are a number of variables to contend with which I'm struggling with.
Here is a series of temperatures (in °C) and how I would like excel to
return them:

0.0 - 10000
0.1 - 10001
5.3 - 10053
28.0 - 10280
28.4 - 10284
-0.5 - 11005
-1.0 - 11010 (Note the second 1 in the synop code denotes a minus)
-15.6 - 11156

I currently have a spreadsheet I use but know I will have a problem
when the temperature falls below zero '0'

Any help would be greatly appreciated as my programming/visual basic
language is sparce.

Many thanks

Keith (Southend)
http://www.southendweather.net
Click to expand...


=10^4+10^3*(A1<0)+ABS(A1*10)

--ron
 
I wonder if someone could help me with this 'number crunching'
problem.

Basically, I'm trying to convert a temperature number back into a 5
digit number (part of meteorological synop code). My problem is there
are a number of variables to contend with which I'm struggling with.
Here is a series of temperatures (in °C) and how I would like excel to
return them:

0.0 - 10000
0.1 - 10001
5.3 - 10053
28.0 - 10280
28.4 - 10284
-0.5 - 11005
-1.0 - 11010 (Note the second 1 in the synop code denotes a minus)
-15.6 - 11156

I currently have a spreadsheet I use but know I will have a problem
when the temperature falls below zero '0'

Any help would be greatly appreciated as my programming/visual basic
language is sparce.

Many thanks

Keith (Southend)
http://www.southendweather.net

Keith,

If your number is in cell A1, then the following formula will give you the
code you are looking for:

=1&(A1<0)+1-1&RIGHT("000"&ABS(A1)*10,3)

This works as follows:

=1
Your results always appear to start with a 1.

(A1<0)+1-1
Tests to see if A1 is negative. The answer to this will be TRUE or FALSE, by
adding and then subtracting 1 you get the same answer as a number 1 or 0.
There is probably a better way to perform that conversion.

&RIGHT("000"&ABS(A1)*10,3)
Multiplies the absolute number by 10 to get a positive integer, concatenates
this to the string "000" and then takes the right hand three characters.

Your examples are all to one decimal place and there is no indication as to
what should happen with temperatures over 100 C, but the above formula works
for all of the cases in your posting.

Regards

Thomas
 
=10^4+10^3*(A1<0)+ABS(A1*10)

--ron
Click to expand...

Wow, just the job.
One other variation you maybe able to help me with, what if I wanted
the first number to be a '2' eg:
28.4 - 20284
-0.5 - 21005

What this signifies is 1**** maximum temp, 2**** minimum temp

Many thanks Ron for your quick reply.

Keith (Southend)
 
If the number is in A1:

=10000+(10*ABS(A1))+1000*(A1<0)
Click to expand...

Thanks Thomas, Dave & Ron, the +10000 works a treat, as always it's
easy when you see the solution before your eyes. The 100C scenario
shouldn't be a problem Thomas in this code as the highest max we're
likely to see is about 55°C Kuwait perhaps!, if we get to 100°C global
warming won't be a problem as the human race will be finished ;-)

I never expected such a fast response.

Thank you all.

Keith (Southend)
 
Thanks Thomas, Dave & Ron, the +10000 works a treat, as always it's
easy when you see the solution before your eyes. The 100C scenario
shouldn't be a problem Thomas in this code as the highest max we're
likely to see is about 55°C Kuwait perhaps!, if we get to 100°C global
warming won't be a problem as the human race will be finished ;-)

I never expected such a fast response.

Thank you all.

Keith (Southend)
Click to expand...

I'm probably pushing my luck here, but here's the final one that would
simplify my spreadsheet results:

I also have a similar code for 24 hour rainfall 7****, which currently
I use:
=VLOOKUP(B2,'Raw Data 190908Rain'!$A$1:$B$171,2,FALSE) ~ 'Raw Data
190908Rain' is another worksheet in the excel /filebook.

This is fine when there is actually rainfall, eg:
0.2 - =SUM(7000+0.2)*10) = 70002
16.8 - =SUM(7000+16.8)*10 = 70168
My problem is when there was no rainfall and no entry because of this,
it then returns:
#N/A
How can get a return of '0' and therefore 70000?
This would be the icing on the cake :-)

Final result adding the previous solution could be as follows:
20103 70000
20103 70002 etc...

Many thanks

Keith (Southend)
 
You can modify your =vlookup() formula to return a 0 if there is no match:
=if(isna(vlookup(...)),0,vlookup(...))

Or you could modify the formula that does the conversion:

=if(isna(a1),70000,yourformulahere)
 
Wow, just the job.
One other variation you maybe able to help me with, what if I wanted
the first number to be a '2' eg:
28.4 - 20284
-0.5 - 21005

What this signifies is 1**** maximum temp, 2**** minimum temp

Many thanks Ron for your quick reply.

Keith (Southend)
Click to expand...

To make the first digit a "2", merely multiply the 10^4 component by 2.

e.g.:

=2*10^4+10^3*(A1<0)+ABS(A1*10)

--ron
 
You can modify your =vlookup() formula to return a 0 if there is no match:
=if(isna(vlookup(...)),0,vlookup(...))

Or you could modify the formula that does the conversion:

=if(isna(a1),70000,yourformulahere)
Click to expand...


Thanks once again Dave, the 1st formula works a treat :-)

Keith (Southend)
 
Back
Top