Thread: Any help is Appreciated (Excel 2003)

1. 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 D6-D36 (CUMULATIVE TOTAL)
D39 / TO ADD EACH OCCURRENCE OF 32F AND BELOW FROM COL E6-E36 (CUMULATIVE TOTAL)
D40 / TO ADD EACH OCCURRENCE OF 00F AND BELOW FROM COL E6-E36 (CUMULATIVE TOTAL)

I38, I39, I40. / TO ADD EACH OCCURRENCE OF PCPN. FROM COLUMN H6-H36
AND DEPOSIT IN CORRECT SLOT EITHER I38, I39, I40.

G6-G36 / TO ADD EACH 'Y' AND TOTAL IN L39, SAME WITH J6-J36 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??

2. 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

3. 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, Ctrl-Alt-Enter 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

4. 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.

5. 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

6. 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.

7. Re: Any help is Appreciated (Excel 2003)

D38: It works fine for me (gives 2)
D39/D40 Missed the fact they were e6-E36. 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 ctrl-shift-enter. Number is 680

L40- So put it into L45 and it works fine.

See the attached.
Steve

8. 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 D6-13, E6-13, B44B45, & E44E45, show up ##### can we get rid of that?
I Thank You again. Ed

9. 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

10. 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.

11. 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.

12. 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

13. 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

14. Re: Any help is Appreciated (Excel 2003)

You are so kind, Thank You. This makes it so much easier.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•