| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
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" wrote: 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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
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" wrote: 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" wrote: 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" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
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. "Sheeloo" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
Picman wrote:
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)) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
Try in B3
=IF(AND(B1=0,B2<0),1,IF(AND(B1<0,B2=0),0,"B1 and B2 both have values")) "Picman" wrote: 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. "Sheeloo" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
If both are "0" then blank ("").
"Glenn" wrote: Picman wrote: 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)) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
If both have values then i want to calculate the percentage of freight to
sales. "Sheeloo" wrote: Try in B3 =IF(AND(B1=0,B2<0),1,IF(AND(B1<0,B2=0),0,"B1 and B2 both have values")) "Picman" wrote: 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. "Sheeloo" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
Picman wrote:
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. "Glenn" wrote: =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 wrote: 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)) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
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" wrote: If both have values then i want to calculate the percentage of freight to sales. "Sheeloo" wrote: Try in B3 =IF(AND(B1=0,B2<0),1,IF(AND(B1<0,B2=0),0,"B1 and B2 both have values")) "Picman" wrote: 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. "Sheeloo" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
That worked and it all makes sense, thank you very much for solving my brain
cramp. "Sean Timmons" wrote: 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" wrote: If both have values then i want to calculate the percentage of freight to sales. "Sheeloo" wrote: Try in B3 =IF(AND(B1=0,B2<0),1,IF(AND(B1<0,B2=0),0,"B1 and B2 both have values")) "Picman" wrote: 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. "Sheeloo" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions
Thanks Glen that worked as well.
"Glenn" wrote: Picman wrote: 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. "Glenn" wrote: =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 wrote: 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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
| Multiple conditions and multiple return values | Excel Worksheet Functions | |||
| Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Linear Mode
