Welcome Guest
Log In | Register )
You last visited December 5, 2016, 11:30 am
All times shown are
Eastern Time (GMT-5:00)

Need Excel formula

Topic closed. 11 replies. Last post 6 years ago by hotshot4.

Page 1 of 1
PrintE-mailLink
Avatar
Iron Station, NC
United States
Member #74828
May 21, 2009
69 Posts
Offline
Posted: July 27, 2010, 1:13 pm - IP Logged

I have an Excel worksheet where I enter the Power Ball winning number by day of week

I input Wednesday winning number then over a few columns I enter Saturday’s numbers

At the bottom of each column I have several formulas to find the Min & Max number, and the number of times each number has been hit, each week I insert a new row to enter that week’s numbers and

My formula at the bottom changes but I don’t want it to change. Here is my problem

I use cell E4 to enter the first digit on the winning numbers for that day, my formula at the bottom is

=Min($E$4;$E221) this changes to =min($E$5:$E221) and all the other formula’s change also. How can I make it stay as $E$4 when I insert a new row.   Thanks  Bash

    Avatar
    Iron Station, NC
    United States
    Member #74828
    May 21, 2009
    69 Posts
    Offline
    Posted: July 27, 2010, 7:15 pm - IP Logged

    That is when I insert a new row, the formula at the end of the column chanes for $E4$4 to $E$5, as I insert a new row the number changes each time.

      Avatar

      United States
      Member #41846
      June 23, 2006
      459 Posts
      Offline
      Posted: July 27, 2010, 9:15 pm - IP Logged

      put your formulas at the top of the page. when you insert a new line( below the formulas) they won't be affected.

        Avatar

        United States
        Member #83701
        December 13, 2009
        225 Posts
        Offline
        Posted: July 27, 2010, 10:04 pm - IP Logged

        I have an Excel worksheet where I enter the Power Ball winning number by day of week

        I input Wednesday winning number then over a few columns I enter Saturday’s numbers

        At the bottom of each column I have several formulas to find the Min & Max number, and the number of times each number has been hit, each week I insert a new row to enter that week’s numbers and

        My formula at the bottom changes but I don’t want it to change. Here is my problem

        I use cell E4 to enter the first digit on the winning numbers for that day, my formula at the bottom is

        =Min($E$4;$E221) this changes to =min($E$5:$E221) and all the other formula’s change also. How can I make it stay as $E$4 when I insert a new row.   Thanks  Bash

        First Copy E4 to E5, then be on E5 when you insert a new row.

          Avatar
          Iron Station, NC
          United States
          Member #74828
          May 21, 2009
          69 Posts
          Offline
          Posted: July 28, 2010, 8:25 am - IP Logged

          Celll E5 has last weeks first digit number in it, E4 becomes E5 after I insert a new row.

                        Cell D                   Cell E     Cell F

          Row 4   Wed July-28-10      2        has second digit of Pwer Ball so on so forth

          Row  5  Wed July-21-10       3

          Row  6  Wed July -14-10      1

           

          Row 222    Min                  =Min($E$4;$E221)

          Row 223     Max                 =Max($E$4:$E221)

          Row 224   different formula 

          When I insert a new row in row 4 the above formula changes from $E$4 to $E$5 automatic and I need for it to stay $E$4

          HELP and Thanks

            Raven62's avatar - binary
            New Jersey
            United States
            Member #17843
            June 28, 2005
            49693 Posts
            Offline
            Posted: July 28, 2010, 11:35 am - IP Logged

            Celll E5 has last weeks first digit number in it, E4 becomes E5 after I insert a new row.

                          Cell D                   Cell E     Cell F

            Row 4   Wed July-28-10      2        has second digit of Pwer Ball so on so forth

            Row  5  Wed July-21-10       3

            Row  6  Wed July -14-10      1

             

            Row 222    Min                  =Min($E$4;$E221)

            Row 223     Max                 =Max($E$4:$E221)

            Row 224   different formula 

            When I insert a new row in row 4 the above formula changes from $E$4 to $E$5 automatic and I need for it to stay $E$4

            HELP and Thanks

            Have you tried doing: "Shift Cells Down" :to Make Room for new data...

            A mind once stretched by a new idea never returns to its original dimensions!

              Avatar
              Iron Station, NC
              United States
              Member #74828
              May 21, 2009
              69 Posts
              Offline
              Posted: July 28, 2010, 2:03 pm - IP Logged

              Be glad to try it, but please explain how to do it, sorry to be so dumb but I've never heard of it, know  how to do it. 

              Thanks

                winsumloosesum's avatar - Lottery-060.jpg
                Pennsylvania
                United States
                Member #2218
                September 1, 2003
                5387 Posts
                Offline
                Posted: August 1, 2010, 3:42 pm - IP Logged

                I have an Excel worksheet where I enter the Power Ball winning number by day of week

                I input Wednesday winning number then over a few columns I enter Saturday’s numbers

                At the bottom of each column I have several formulas to find the Min & Max number, and the number of times each number has been hit, each week I insert a new row to enter that week’s numbers and

                My formula at the bottom changes but I don’t want it to change. Here is my problem

                I use cell E4 to enter the first digit on the winning numbers for that day, my formula at the bottom is

                =Min($E$4;$E221) this changes to =min($E$5:$E221) and all the other formula’s change also. How can I make it stay as $E$4 when I insert a new row.   Thanks  Bash

                hotshot,

                Try this:

                Download link to PowerBall Excel file:

                http://www.box.net/shared/os80gb4kdq

                  Avatar
                  Iron Station, NC
                  United States
                  Member #74828
                  May 21, 2009
                  69 Posts
                  Offline
                  Posted: August 2, 2010, 8:22 am - IP Logged

                  Thanks, I've already got one like that, but added it to my Excel files, but I still would like for someone to give me the information I request

                  for my work sheet.   Thanks

                    aspire's avatar - terlion
                    New Member
                    Taipei
                    Taiwan
                    Member #70314
                    January 29, 2009
                    2 Posts
                    Offline
                    Posted: August 3, 2010, 12:11 pm - IP Logged

                    That is when I insert a new row, the formula at the end of the column chanes for $E4$4 to $E$5, as I insert a new row the number changes each time.

                    leave cell E4 blank for the purpose of entering your predicted number for the next draw, always insert a new row after row 4 for the new data, then your formula will not change.

                      Guest


                      Member #0
                      January 1, 2000
                      0 Posts
                      Offline
                      Posted: August 10, 2010, 11:37 pm - IP Logged

                      I have an Excel worksheet where I enter the Power Ball winning number by day of week

                      I input Wednesday winning number then over a few columns I enter Saturday’s numbers

                      At the bottom of each column I have several formulas to find the Min & Max number, and the number of times each number has been hit, each week I insert a new row to enter that week’s numbers and

                      My formula at the bottom changes but I don’t want it to change. Here is my problem

                      I use cell E4 to enter the first digit on the winning numbers for that day, my formula at the bottom is

                      =Min($E$4;$E221) this changes to =min($E$5:$E221) and all the other formula’s change also. How can I make it stay as $E$4 when I insert a new row.   Thanks  Bash

                      use the 'indirect' in the formula.

                      =min(indirect($e$4:$e221)) 

                      try it and let me know .

                        Avatar
                        Iron Station, NC
                        United States
                        Member #74828
                        May 21, 2009
                        69 Posts
                        Offline
                        Posted: August 11, 2010, 8:47 am - IP Logged

                        That formula states   #Value    (A function in the formula causes the result to change each time the

                        spreadsheet is calculated. The final evacuation step will make the result in the cell but intern steps may not).

                        Your close but not there yet, please see if you can help, and thanks for trying.