Any help is Appreciated (Excel 2003)

Topic

New Reply

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??

Reply | Quote

Replies

Column D/E looks at col B/C and when they are blank the “value” is zero and the 0

Reply | Quote

Column D/E looks at col B/C and when they are blank the “value” is zero and the 0

Reply | Quote

After using Steve’s fixes add these.

D38 – =COUNTIF(D6:D36,”>=90″)

D39 – =COUNTIF(D6:D36,”<=32")

D40 – =COUNTIF(D6:D36,"<=0")

I38 – =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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 D6:D36
D39:

 =COUNTIF(e6:e36,"<=32")

D40

 =COUNTIF(e6:e36,"<=0")

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 select the range G6:L36.
[All the data cells will be selected]
Insert- name- define
Enter a name, for example, data

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 . The data is cleared, formulas are still there.

You could also create a macro if desired.

Steve

Reply | Quote

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 select the range G6:L36.
[All the data cells will be selected]
Insert- name- define
Enter a name, for example, data

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 . The data is cleared, formulas are still there.

You could also create a macro if desired.

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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 D6:D36
D39:

 =COUNTIF(e6:e36,"<=32")

D40

 =COUNTIF(e6:e36,"<=0")

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

After using Steve’s fixes add these.

D38 – =COUNTIF(D6:D36,”>=90″)

D39 – =COUNTIF(D6:D36,”<=32")

D40 – =COUNTIF(D6:D36,"<=0")

I38 – =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

Reply | Quote