Multiple conditions

P

Picman

I have to calculate a freight percentage based on 2 cells that on occation
will not always both have a value present.

Most of the time it looks like this.

Sales $100.00
Freight $25.00
Percent 25.00%

Sometimes I will be missing one or the other number (see below). When the
sales number is not there and the freight is, then I need the percentage to
be 100.00% and when the freight isn't there and the sales are, the percentage
needs to read 0.00%.

Sales $0.00
Freight $25.00
Percent 25.00%

or

Sales $100.00
Freight $0.00
Percent 0.00%

I know this should be easy but I've tried different combinations of "If" and
"AND" statements but can't get it to work.
 
S

Sheeloo

Assuming Sales, Freight and Percentages are in A2, B2 and C2 then enter this
in D2

=IF(AND(A2=0,B2<>0),1,IF(AND(A2<>0,B2=0),0,C2))
 
P

Picman

Sales is in B1 and freight is in b2 and the caluculation would need to be in
B3. I just included it to show what the end result should look like.
 
S

Sheeloo

Then use this in B4

=IF(AND(B1=0,B2<>0),1,IF(AND(B1<>0,B2=0),0,B3))

In Excel a FORMULA can not refer to the its own cell so you need to have
four cells...
 
P

Picman

I think you misunderstood me. B3 is where I need to build the formula to
calculate the percentage of the freight (B2) to the sales (B1) the 2 pieces
of data that I have.
 
G

Glenn

Picman said:
I have to calculate a freight percentage based on 2 cells that on occation
will not always both have a value present.

Most of the time it looks like this.

Sales $100.00
Freight $25.00
Percent 25.00%

Sometimes I will be missing one or the other number (see below). When the
sales number is not there and the freight is, then I need the percentage to
be 100.00% and when the freight isn't there and the sales are, the percentage
needs to read 0.00%.

Sales $0.00
Freight $25.00
Percent 25.00%

or

Sales $100.00
Freight $0.00
Percent 0.00%

I know this should be easy but I've tried different combinations of "If" and
"AND" statements but can't get it to work.


=IF(SALES=0,IF(FREIGHT=0,"You don't say what you want if both are
0",1),IF(FREIGHT=0,0,FREIGHT/SALES))
 
S

Sheeloo

Try in B3

=IF(AND(B1=0,B2<>0),1,IF(AND(B1<>0,B2=0),0,"B1 and B2 both have values"))
 
P

Picman

If both have values then i want to calculate the percentage of freight to
sales.
 
G

Glenn

If both are "0" then blank ("").


OK, so remove the text between the quotes. Adding your cell references from
elsewhere in the thread:

=IF(B1=0,IF(B2=0,"",1),IF(B2=0,0,B2/B1))
 
S

Sean Timmons

Well then

=IF(AND(B1=0,B2<>0),1,IF(AND(B1<>0,B2=0),0,B2/B1))

that should be an easy one to calculate.................................
 
P

Picman

Thanks Glen that worked as well.

Glenn said:
OK, so remove the text between the quotes. Adding your cell references from
elsewhere in the thread:

=IF(B1=0,IF(B2=0,"",1),IF(B2=0,0,B2/B1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top