Multiple conditions

[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.

 

[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))

 

[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.

 

[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...

 

[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.

 

[Glenn]

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))

 

[Sheeloo]

Try in B3

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

 

[Picman]

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

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

 

[Picman]

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

 

[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))

 

[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.................................

 

[Picman]

That worked and it all makes sense, thank you very much for solving my brain
cramp.

 

[Picman]

Thanks Glen that worked as well.

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))