Question for excel guru's

[neurosis]

Im creating an excel file to calculate my drill speeds/feeds/chiploads/etc automatically. This isnt something important, its just a project to help me learn excel. Anyway, When I enter a formula and copy it down, it changes the values accordingly (a1 becomes a2 etc). Is there a way to copy the cell formula down yet have it leave "ONE" of the values to a single cell in all of the copies? so if you were doing =a2+f2 then copy down would be a3+f3, I would like to have it be a3+f2 leaving the f2 for all of the copies. Is this possible?

[Günther Massimo - GMCCS]

Try F$2$

[neurosis]

It didnt work. Maybe I should also state that I am using open office instead of actual excel. would that make a difference?

[Slydman]

No it's F$2 only

[neurosis]

That worked! Thanks.

[Jerosu]

Also, a shortcut to get the absolute reference is to press F4 in Excel, or Shift+F4 in Open Office after you either type in a cell value, or click on a cell to input that value in a formula.

 

Example for Open Office:

in a cell enter "=B2" then press Shift+F4.

It will change to $B$2, as you press Shift+F4 additional times it will toggle through $B2, B$2, $B$2. What that is doing is giving you an abolute reference to the column, the row, or the column and row.

 

The example above didn't work because the $ needs to proceed the column or row, so F$2$ is invalid (should be $F$2). F$2 is absolute reference to only row 2 although it works for your application apparently.

[neurosis]

One more question. Bare with me here... This is my first day even attempting to mess with excel. Now I have my columns set how I want them. When I copy the formula's down the rows it automatically enters numbers or text when there is nothing for it to go by. How do I keep it from entering the garbage unless there is something for it to reference? I hope that made sense. I removed the formulas from the rest of the rows and only have two rows entered right now for testing. I put it on the ftp site

Text_&_post_files_&_misc/DRILL_CHIP_LOAD_calculator.xls

if anyone wants to check it out. I could use some suggestions. Im going to be using it to help me create my tool libraries. I'll make another one for endmills etc.

[Jerosu]

The easiest thing for you might be to use a formula (using your chipload column formula as an example) like:

code:

---

=IF(ISBLANK(B3)=0;(IF(B3/64<$L$9;B3/64;$L$9));"blank")

---

Replace "blank" in the above formula with "" so it gives you a blank cell. I just put that there for illustration.

 

Note there is a difference between a cell where you don't see anything and a truly blank cell. If you get 0.000 as a result, its because you have a space or other character in the B column. Highlight the cells and press Delete -> Delete All

 

This example is just nesting another IF statement with what you have. It tests to see if B3 is blank. If it is not blank, it uses your formula, if it is blank it gives "". Since the cell reference is not absolute, as you copy it should populate with a cell address from the proper row.

 

You could just copy that formula down the column for a few hundred cells or however many then lock them. To do that without dragging the little crosshair, you can select the cells you want using Shift+PgDn then Edit -> Fill -> Down

 

There are other ways to do this also. This is one might not be the most elegant, but it's pretty simple.

 

For Excel you could use the ISEmpty function

 

[ 03-19-2008, 01:17 AM: Message edited by: Jerosu ]

[neurosis]

Heh.... that got a little complicated. I can see how it works sorta. The only thing i dont quite understand is why after the (B3) you have to have the =0 .

 

One more thing too.. I try the same thing in the chip load reduction column and it doesnt seem to work. Is that because the column that its referencing has a formula in it and is not truly blank? How do you solve that problem? It wants to put a 0 in there.

 

I also notice that

 

code:

---

IF(ISBLANK(B3)=0;IF(B3/64<L$9;B3/64;L$9);"")  

---

works too but im not really sure what the difference is. there are extra () in your original that i forgot to put in but it worked anyway.

 

[ 03-19-2008, 01:58 AM: Message edited by: Neurosis ]

[Jerosu]

Oh sorry. ISBLANK returns true if the cell is empty, false if it is not empty

 

True = 1

False = 0

[neurosis]

Oh crap! I entered the formulas and everything works great in open office. When I open and try to use it in excel it doesnt do the calculations and leaves the cells blank. I'll put the updated version on there. The cells are protected now but with no password.

[Jerosu]

BTW for some reason I was thinking the Excel function is ISEmpty.... That is incorrect. There is no such thing. They both use ISBlank. I was posting at home without access to Excel.

 

Sorry bout that.

[Jerosu]

In Excel use

code:

---

=IF(ISBLANK(B3)=FALSE,IF(B3/64<L$9,B3/64,L$9),"")

---

The difference being ISBLANK(B3)=False

 

My Open Office autocorrected False to 0. I guess that doesn't work in Excel.

 

Learn something everyday

 

Good luck

[neurosis]

Thank you Jerosu. Youve been a ton of help. In open office it works flawlessly! Im going to change it so that it works in excel and save it so that it will work in either. Then im going to add all of the drills and diameters and see the result. If it helps to build my tool library in mastercam i'll start endmill sheet.

 

Thanks again.

 

DRILL_CHIP_LOAD_calculator_FINAL.xls

 

if you want to check it out its done now.

 

[ 03-19-2008, 11:18 AM: Message edited by: Neurosis ]

[neurosis]

OK, spoke too soon. for some reason when it reaches row 111 the calculations for the feedrate stops working. I need help on this one.

[3rdstring]

I don't know if it stopped working, the math works out & its giving you the result to your fomula.

 

 

A cleaner way to write your formula

code:

---

=IF(ISBLANK(B3),"", IF(B3/64<L$9,B3/64,L$9))

---

[neurosis]

The math for the formula quit working. If you look at row 111 in the I column the math doesnt work out. The feed rate column formula says to multiply H (the rpm) and C (the chipload) and if you calculate it out its not doing the math correctly. Its the easiest formula int he whole sheet and for some reason when the rpm falls between the min and max numbers the math fails.

 

code:

---

=IF(ISBLANK(B111)=FALSE,H111*C111,"")  

---

H111 = 7903 and C111 = .0045. The answer should be 35.56 but its coming out to 35.813.

 

The values get worse after that. Row 111 - 126 are all coming up with 35.813 even though the values in the H and C colums are different in every row. its not making any sense to me.

[3rdstring]

Your rounding your numbers, XL isn't. The C111 Feed = @ 0.00453125 & the H111 = @ 7903.44827586207 which results in 35.813.

 

HTH

[3rdstring]

If you want the math in XL to work the way you are calculating it you'll need to incorporate the INT & ROUND functions into your formulas.

[neurosis]

Damn..... the math is working better than I thought. So the fields are rounding numbers "ie the c111 and H111" but the formula is using the whole numbers?? I should have done some calculations before freaking out. I notice the difference in the ipr is only about .00004. Strange that it rounds that way for quite a while.

[3rdstring]

quote:

---

So the fields are rounding numbers "ie the c111 and H111"

---

The cells are only rounding the numbers to display them. XL still knows the full value.

[neurosis]

Well, I finished entering all of the drill diameters less metric sizes up to 1". I cant believe how many feed rates are the same. It was surprising. I calculate them out and they are correct or very close. I would have never imagined.