Some basic excel help.

[Eddie Wall]

I have tried a number of methods to solve 2 problems and just cannot
get it right, I thought conditional formatting might do problem 1 but
no joy, so I would appreciate any guidance .


1) I have a basic spread sheet which is as follows.

Hours Rate Total Acuum

Row 4 would read the total (D4) as =sum( b4*c4)
and the accum(E4) would read =sum(e3+d4)

So I have a number of hours multiplied by a variable rate which is
entered into the total cell for each row., this value is added to the
previous rows accum (total) to give a new ( running ) accum ( total )
on the row.

Each row therefore has Hours, rate, total, accum.

My problem is that I may not know in advance how many rows I need or
may want if I include say a 5-6 extra rows( including the formulae)
to cater for inserting new rows of data I end up with the Total Zero
and the accum column filled with the last accumulated total.

2)

I have a list of parts of my entire stock list with prices. I would
like to be ale to input quantities for a particular part and
automatically total the value(s) ( not a problem). I wold also like to
print out ONLY those rows that have values in the quantities column,
so that I am only printing out actual orders and not the entire stock
sheet each time. In theory it is very straightforward but I just
cannot get it.

Many thanks

Eddie

 

[J.E. McGimpsey]

One way:

D4: =IF(COUNT(B4:C4)>0,B4*C4,"")
E4: =IF(D4<>"",E3+D4,"")


Copy down as far as necessary.

Note that you don't need to wrap math operations in the SUM()
function. For

SUM(E3+D4)

XL calculates E3+D4, then passes the result to SUM(), which, of
course, returns that result.

 

[Eddie Wall]

One way:

D4: =IF(COUNT(B4:C4)>0,B4*C4,"")
E4: =IF(D4<>"",E3+D4,"")


Copy down as far as necessary.

Works perfectly.....most enlightening ...Many thanks.!

it is possible that you could elxplain this one
=IF(COUNT(B4:C4)>0,B4*C4,"") in english..... !! I take it that the "
,B4*C4,"" " component states "Then the value of B4*C4 is null "

Note that you don't need to wrap math operations in the SUM()
function. For

SUM(E3+D4)

XL calculates E3+D4, then passes the result to SUM(), which, of
course, returns that result.

So it is pretty obvious I am clueless !

Much appreciated. !

Eddie

 

[purplehaz]

I'm sure JE can explain it better, but I give it a shot.
Using the IF in excel formulas is like using a IF/Then/Otherwise(else)
statement.
Ex:
If(your expression here=something, then do this, if not then do this)
so.....
If(the count in the range b4 to c4 is greater than 0, then give me b4*c4, if
its not leave it blank)

Hope this helps.

 

[Eddie Wall]

I'm sure JE can explain it better, but I give it a shot.
Using the IF in excel formulas is like using a IF/Then/Otherwise(else)
statement.
Ex:
If(your expression here=something, then do this, if not then do this)
so.....
If(the count in the range b4 to c4 is greater than 0, then give me b4*c4, if
its not leave it blank)

Hope this helps.

Perfectly... many thanks. !


Eddie

 

[purplehaz]

You're welcome.

Perfectly... many thanks. !


Eddie