Welcome Guest
You last visited December 14, 2017, 11:43 am
All times shown are
Eastern Time (GMT-5:00)

# Need Excel formula

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

 Page 1 of 1
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

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.

United States
Member #41846
June 23, 2006
472 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.

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

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

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

New Jersey
United States
Member #17843
June 28, 2005
71366 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!

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

Pennsylvania
United States
Member #2218
September 1, 2003
5641 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

hotshot,

Try this:

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

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

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

use the 'indirect' in the formula.

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

try it and let me know .

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.

 Page 1 of 1