Results 1 to 14 of 14
  1. #1
    New Lounger
    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 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. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Lounger
    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, 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. #4
    New Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    New Lounger
    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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. #8
    New Lounger
    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 D6-13, E6-13, B44B45, & E44E45, show up ##### can we get rid of that?
    I Thank You again. Ed

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    New Lounger
    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.

  11. #11
    Uranium Lounger
    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

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

  14. #14
    New Lounger
    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.

Posting Permissions

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