• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

only want to multiply certain cells

Danin

Danin

New Member
I Have 2 sub total columns of different parts of an order. the totals get filled from other cells.
I only want to show the total of Subtotal column 1 multiplied by Rate column in NetTotal Column if both subtotal columns are full

I want to only ever multiply the rate by the first subtotal if both subtotal columns are filled.

Subtotal1 Subtotal2 Rate NetTotal

Subtotal1 x Rate = NetTotal
Subtotal2 x Rate = NetTotal
If Subtotal 1 and Subtotal 2 have values. I only want to use Subtotal 1 value x Rate = NetTotal.
 
Thank you Narayan, I managed to get that far. It works well if both cells have a value in them. but what if only subtotal 2 has a value in it? I want it to calculate subtotal 2 x rate
subtotal 1 x rate
if both cells are filled. then it will only calculate subtotal 1.

I didn't explain the problem fully. my fault. I need that little extra that will say. ok. if a one of cells has a quantity in it, then multiply by rate.
if both cells have a quantity in it, then only multiply rate by subtotal 1.

if subtotal 1 does not equal zero, and subtotal 2 does not equal zero, only multiply subtotal 1 x rate. if subtotal 2 does not equal zero and subtotal 1 does equal zero, multiply subtotal 2 x rate. =IF(AND($H255<>"",$L255<>""),$H255*$N255,"")*IF(AND($L255<>"",$H255<>FALSE),$L255*$N255,"")

?Didn't work. i tried
 
Hi ,

The construct of your IF statement should be :

=IF(AND($H255<>"",$L255<>"") , $H255*$N255 , IF($L255<>"" , $L255*$N255 , ""))

If the highlighted portion is not satisfied , it means that either H255 or L255 or both are not having data in them.

In this case , the second IF statement is checking to see if L255 has a value in it ; if it has , then the result L255 * N255 is output.

If L255 does not have a value in it , it does not matter whether H255 has a value in it or not ; the output is a null string.

Narayan
 
Thank you for your help. I am still not getting the answer I need. I can get the first part of the formula. I can't get work out how to add the last bit of the equation to the formula.

If H255 and L255 have numbers in them. ONLY use H255 * Rate. (yes formulas work)
IF only H255 has number in it and L255 is empty. than multiply H255*rate (yes formula works)
If only L255 has number in it and H255 is empty. multiply L255*rate. (No formula does not include this)

I can get the first 2 lines. but I can't get the last bit.
 
Hi ,

When the logic is explained clearly , the formula is easier to develop.

=IF(AND($H255<>"",$L255<>"") , $H255*$N255 , IF($H255<>"" , $H255*$N255 , IF($L255<>"" , $L255*$N255 , "")))

Narayan
 
Thank you for your input unfortunatley the problem still exists.
the formula isn't multiplying L255*N255 if H255 is empty. your formula still doesn't allow for this part of equation.
maybe it isn't possible and i need to look at it a different way.
 
I think the problem is the Bold part of formula. it is giving blanket statement to all of the formula that if H255 and or L255 is not empty than only use H255 calculation. so the formulas after won't use l255 even if H255 is empty.
 
OMG I GOT IT. WOOHOO!!
Ok only the first Bold part needs fixing. i changed the cell. i have a VLOOKUP for different types of items. it is only one item that L255 will ever use for calculation.

=IF(AND(H255<>"",E255<>"LABOUR"), add rest of formula. it solves this one problem but i still need a blanket solution but this is urgent to get solved.
 
Danin said:
I think the problem is the Bold part of formula. it is giving blanket statement to all of the formula that if H255 and or L255 is not empty than only use H255 calculation. so the formulas after won't use l255 even if H255 is empty.
Click to expand...
Hi ,

The AND function does not implement an and / or ; it implements an AND.

The highlighted portion of the formula will never be satisfied if either one of the cells is blank. Only if both cells have data in them will it be satisfied.

Thus , when we come to the further portions of the formula , the only possibilities are :

1. H255 is blank

2. L255 is blank

3. Both H255 and L255 are blank

The following formula caters to all of the above 3 conditions :

=IF(AND($H255<>"",$L255<>"") , $H255*$N255 , IF($H255<>"" , $H255*$N255 , IF($L255<>"" , $L255*$N255 , "")))

If 1 is satsified , the output is L255 * N255

If 2 is satisfied , the output is H255 * N255

If 3 is satisfied , the output is blank.

In the absence of a workbook with data in it , there is nothing more I can say.

Narayan
 
Thank you for all your help. from the above. I believe the equation is solved. would tick a box as solved if there was one :)
 
You must log in or register to reply here.
Back
Top