Results 1 to 14 of 14

20040528, 17:27 #1
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Any help is Appreciated (Excel 2003)
Have done what I could, which isn't much. Trying to make things easier at work. I'll thank you in advance. Ed
D38 / TO ADD EACH OCCURRENCE OF 90F AND ABOVE FROM COL D6D36 (CUMULATIVE TOTAL)
D39 / TO ADD EACH OCCURRENCE OF 32F AND BELOW FROM COL E6E36 (CUMULATIVE TOTAL)
D40 / TO ADD EACH OCCURRENCE OF 00F AND BELOW FROM COL E6E36 (CUMULATIVE TOTAL)
I38, I39, I40. / TO ADD EACH OCCURRENCE OF PCPN. FROM COLUMN H6H36
AND DEPOSIT IN CORRECT SLOT EITHER I38, I39, I40.
G6G36 / TO ADD EACH 'Y' AND TOTAL IN L39, SAME WITH J6J36 TOTAL IN L38 (CUMULATIVE TOTAL)
L45 HAVE THE WIND DIRECTION THAT GOES WITH THE HIGHEST GUST SHOW UP
AFTER SPEED IS PUT IN.
BECAUSE OF FORMULA ADDED TO CONVERT TEMP. IT MAKES COLUMN D,E, F SHOW UP AS 32 WHY??

20040528, 18:58 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Any help is Appreciated (Excel 2003)
Column D/E looks at col B/C and when they are blank the "value" is zero and the 0

20040528, 19:41 #3
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
After using Steve's fixes add these.
D38  =COUNTIF(D636,">=90")
D39  =COUNTIF(D636,"<=32")
D40  =COUNTIF(D636,"<=0")
I38  =COUNTIF(H6:H36,"<=.10")
I39  =COUNTIF(H6:H36,">.10")COUNTIF(H6:H36,">.50")
I40  =COUNTIF(H6:H36,">.50")
L38  =SUM(IF((J6:J36="Y"),K6:K36)) this is an array formula, CtrlAltEnter to when entering it.
L39  =COUNTIF(J6:J36,"Y")
L40  =OFFSET(L6,MATCH(L44,L6:L36,0)1,1,1,1)
I am pretty sure these should do it for you.
yoyophil

20040528, 23:48 #4
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
yoyophil
It seems that they mostly get excepted as a formula, however they don't count the data in the range.
I couldn't get any of these to work, what steve gave me worked. Maybe you can try them on the spreadsheet itself.
Thanks for your time.

20040529, 00:48 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Any help is Appreciated (Excel 2003)
Which ones do not work as you expect? They seem to work as I would expect in your file.
What number do they give and what do you expect in those cells?
Steve

20040529, 02:54 #6
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
OK, This is what I received After I copied and pasted the formulas.
D38 The formula you entered contains an error message.
D39 (32 or Below) Doesn't pick up the 2 below 32 temps. in E6&E7
D40 (00 OR Below) Doesn't pick up the 2 below 00 temps in E12&E13
I38 Doesn't pick up the 2 below .10 in H10&11
I39 Formula error
I40 Found error in formula offers correction, correction doesn't work.
L38 #Value comes up
L39 Seems OK
L40 Works but I need it in Col L45
Again Thank You for your time.

20040529, 09:45 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Any help is Appreciated (Excel 2003)
D38: It works fine for me (gives 2)
D39/D40 Missed the fact they were e6E36. If you look at both formulas they are set for D636
D39:
<pre> =COUNTIF(e6:e36,"<=32")</pre>
D40
<pre> =COUNTIF(e6:e36,"<=0")</pre>
I38 Works fine for me (gives 2)
I39 Works fine for me (gives 2)
I40 Works fine for me (gives 4)
L38 You did not follow directions given and confirm with ctrlshiftenter. Number is 680
L40 So put it into L45 and it works fine.
See the attached.
Steve

20040529, 16:36 #8
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
Steve
Looks like you did it  you da man. I had some trouble following directions and with trying not to let you know
how inept I am when it comes to this. One last thing, on attachment for some reason D613, E613, B44B45, & E44E45, show up ##### can we get rid of that?
I Thank You again. Ed

20040529, 18:03 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Any help is Appreciated (Excel 2003)
Cells B6:C13 are not blank. The contain a space. Since they are not blank, excel tries to convert them and it can't convert text.
Select B6:C13 and <delete>
If you want to use spaces to clear rather than deleting the contents use in D6 a formula like:
=IF(TRIM(B6)="","",CONVERT(B6,"C","F"))
Then copy this to D6:E36
Steve

20040529, 20:19 #10
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
I have tried to delete the contents of the cells without deleting the formulas so I can use the same format month to month. It seems that
when I try to clear just the data it still deletes the formula no matter if I use keyboard delete or backspace or from the menu bar clear contents. I want to keep all
formulas in place and just delete the data month to month.

20040530, 01:12 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
You can't have data and a formula in the same cell. Therefore, you should be able to select just the cells with data and hit the delete key. If the cells with data don't change, then you coule even write or record a macro do delete the data and make sure you don't accidentally delete a formula.
Legare Coleman

20040530, 10:22 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Any help is Appreciated (Excel 2003)
Followup to Legare's post:
Just delete the cells B6:C36 and the cells G6:L36. DOn't delete the cells D6:F36 in the "data range" since they have formulas;
To do this easily you could create a name:
Select B6:C36
while hodking <Ctrl> select the range G6:L36.
[All the data cells will be selected]
Insert name define
Enter a name, for example, data
<ok>
Now whenever you want to delete the range, in the name box (upper left, to the left of formula bar), select "data" from the pulldown and then press <delete>. The data is cleared, formulas are still there.
You could also create a macro if desired.
Steve

20040601, 20:49 #13
 Join Date
 Feb 2004
 Location
 Buffalo, New York, Wales
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
Sorry about the wrong range with some of my formulas. I have attached a copy of your spreadsheet with the formulas as requested. I have also adjusted some others so errors are not shown when the sheet is cleared of any entries. I also used the macro recorder to give you a CLEAR button which removes the entries so you delete old entries and start on the next month.
yoyophil

20040601, 21:15 #14
 Join Date
 May 2004
 Location
 Under a Bridge, Massachusetts, USA
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Any help is Appreciated (Excel 2003)
You are so kind, Thank You. This makes it so much easier.