help with spreadsheet formula

This is a formula in a spreadsheet I have made for calculating component sizes for cutting panels out of mdf:

=IF((E7=0),(C7+D7-4),(C7+D7+E7-8 ))

This works fine as long as there are values in C7 & D7, but my problem is that if I want to leave a row blank (to create a break between different sets of items) then the formula will give a value of -4, which messes up the total when I add the column. I'm sure there must be a way around this but it's making my brain ache trying to see it, can anyone help?

hermes said:

This is a formula in a spreadsheet I have made for calculating component sizes for cutting panels out of mdf:

=IF(( E7=0),(C7+D7-4),(C7+D7+E7-8 ))

This works fine as long as there are values in C7 & D7, but my problem is that if I want to leave a row blank (to create a break between different sets of items) then the formula will give a value of -4, which messes up the total when I add the column. I'm sure there must be a way around this but it's making my brain ache trying to see it, can anyone help?

Click to expand...

M$ Excel2000 help said:

Move or copy a formula
When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; relative cell references will change.
For more information about absolute and relative references, search for help on The difference between relative and absolute references

Select the cell that contains the formula you want to move or copy.

Point to the border of the selection.

To move the cell, drag the selection to the upper-left cell of the paste area. Microsoft Excel replaces any existing data in the paste area.

To copy the cell, hold down CTRL as you drag.

Tip You can also copy formulas into adjacent cells by using the fill handle. Select the cell that contains the formula, and then drag the fill handle over the range you want to fill.

Click to expand...

Be aware of the two 'copy' methods - bold text quoted above - using the first you can 'drop' the copy in a target cell, bypassing required blanks.

If the formula column exists, ie you have drag copied down the column .... Just select the cells in the redundant row, right click mouse, choose 'Clear Contents' No formula = no result.

Somehow I think you may have a slightly different issue like having -4 whenever there are no values in cells Cn Dn En.
=IF( SUM ( C2:E2 )=0 ,"", IF ( ( E2=0 ) , ( C2+D2-4 ) , (C2+D2+E2-8 ) ) )
This checks, for zero summation row cells C through E, if the cells sum to zero use null string (No space between speech marks "" not " ") as result... literally blank !
Sort of .. IF SumCells=0 Write nowt, ELSE do your original function.

empip said:

Somehow I think you may have a slightly different issue like having -4 whenever there are no values in cells Cn Dn En.
=IF( SUM ( C2:E2 )=0 ,"", IF ( ( E2=0 ) , ( C2+D2-4 ) , (C2+D2+E2-8 ) ) )
:

Click to expand...

Thanks pip, I think this will work, I didn't know you could use IF twice in one formula.

I have another one for you which I have been working on this morning, but which may use the same method if I can get my head around it.
I want to create a formula whichwill be something like this:

=IF((B4>1500<1600),"",=F4)

so that if a particlular length falls between 1500 and 1600mm then the result will equal whatever is in F4 (which is a quantity). However, this way of using < and > together will not work.

= IF ( AND ( B4>1500 , B4<1600 ) ,"", F4 )

If B4 is greater than 1500 and less than 1600, show blank, else, show contents/result from F4.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

= ( ( ( B4>1500 ) + ( B4<1600 ) ) -1 )* F4

B4 > 1500 TRUE (1) FALSE (0)
B4 < 1600 TRUE (1) FALSE (0)
If both TRUE sum is 2 ... -1 = 1......... 1* F4 returns numerical value in F4
When one result is FALSE the sum is 1 ... -1 = 0 ..... 0 * F4 returns num. zero.

empip said:

= IF ( AND ( B4>1500 , B4<1600 ) ,"", F4 )

If B4 is greater than 1500 and less than 1600, show blank, else, show contents/result from F4.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

= ( ( ( B4>1500 ) + ( B4<1600 ) ) -1 )* F4

B4 > 1500 TRUE (1) FALSE (0)
B4 < 1600 TRUE (1) FALSE (0)
If both TRUE sum is 2 ... -1 = 1......... 1* F4 returns numerical value in F4
When one result is FALSE the sum is 1 ... -1 = 0 ..... 0 * F4 returns num. zero.

Click to expand...

Managed to get it sorted in a slightly more convoluted way than yours. In your earlier formula I found that I had to replace "" with 0 in each case, or formulae in cells further down the line would not work, but that's ok as I can hide the zero values through Options. Thanks for the help, it was most useful.

hermes said:

Managed to get it sorted in a slightly more convoluted way than yours. In your earlier formula I found that I had to replace "" with 0 in each case, or formulae in cells further down the line would not work, but that's ok as I can hide the zero values through Options. Thanks for the help, it was most useful.

Click to expand...

I tended to work with the null string .. because Countblanks function counts that but not the space character....

IsBlank is true for a cell with no contents.

Which is all probably muddying the water ....