The tutorial shows how to use IF together with the AND function in Excel to check multiple conditions in one formula.
Some things in the world are finite. Others are infinite, and the IF function seems to be one of such things. On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time.
IF AND statement in Excel
In order to build the IF AND statement, you obviously need to combine the IF and AND functions in one formula. Here's how:
Translated into plain English, the formula reads as follows: IF condition 1 is true AND condition 2 is true, do one thing, otherwise do something else.
As an example, let's make a formula that checks if B2 is "delivered" and C2 is not empty, and depending on the results, does one of the following:
- If both conditions are TRUE, mark the order as "Closed".
- If either condition is FALSE or both are FALSE, then return an empty string ("").
=IF(AND(B2="delivered", C2<>""), "Closed", "")
The screenshot below shows the IF AND function in Excel:
If you'd like to return some value in case the logical test evaluates to FALSE, supply that value in the value_if_false argument. For example:
=IF(AND(B2="delivered", C2<>""), "Closed", "Open")
The modified formula outputs "Closed" if column B is "delivered" and C has any date in it (non-blank). In all other cases, it returns "Open":
Note. When using an IF AND formula in Excel to evaluate text conditions, please keep in mind that lowercase and uppercase are treated as the same character. If you are looking for a case-sensitive IF AND formula, wrap one or more arguments of AND into the EXACT function as it is done in the linked example.
Now that you know the syntax of the Excel IF AND statement, let me show you what kind of tasks it can solve.
Excel IF: greater than AND less than
In the previous example, we were testing two conditions in two different cells. But sometimes you may need to run two or more tests on the same cell. A typical example is checking if a cell value is between two numbers. The Excel IF AND function can easily do that too!
Let's say you have some sales numbers in column B and you are requested to flag the amounts greater than $50 but less than $100. To have it done, insert this formula in C2 and then copy it down the column:
=IF(AND(B2>50, B2<100), "x", "")
If you need to include the boundary values (50 and 100), use the less than or equal to operator (<=) and greater than or equal to (>=) operator:
=IF(AND(B2>=50, B2<=100), "x", "")
To process some other boundary values without changing the formula, enter the minimum and maximum numbers in two separate cells and refer to those cells in your formula. For the formula to work correctly in all the rows, be sure to use absolute references for the boundary cells ($F$1 and $F$2 in our case):
=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")
By using a similar formula, you can check if a date falls within a specified range.
For example, let's flag dates between 10-Sep-2018 and 30-Sep-2018, inclusive. A small hurdle is that dates cannot be supplied to the logical tests directly. For Excel to understand the dates, they should be enclosed in the DATEVALUE function, like this:
=IF(AND(B2>=DATEVALUE("9/10/2018"), B2<=DATEVALUE("9/30/2018")), "x", "")
Or simply input the From and To dates in two cells ($F$1 and $F$2 in this example) and "pull" them from those cells by using the already familiar IF AND formula:
=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")
For more information, please see Excel IF statement between two numbers or dates.
IF this AND that, then calculate something
Apart from returning predefined values, the Excel IF AND function can also perform different calculations depending on whether the specified conditions are TRUE or FALSE.
To demonstrate the approach, we will be calculating a bonus of 5% for "Closed" sales with the amount greater than or equal to $100.
Assuming the amount is in column B and the order status in column C, the formula goes as follows:
=IF(AND(B2>=100, C2="closed"), B2*10%, 0)
The above formula assigns zero to the rest of the orders (value_if_false = 0). If you are willing to give a small stimulating bonus, say 3%, to orders that do not meet the conditions, include the corresponding equation in the value_if_false argument:
=IF(AND(B2>=100, C2="closed"), B2*10%, B2*3%)
Multiple IF AND statements in Excel
As you may have noticed, we have evaluated only two criteria in all the above examples. But there is nothing that would prevent you from including three and more tests in your IF AND formulas as long as they comply with these general limitations of Excel:
- In Excel 2007 and higher, up to 255 arguments can be used in a formula, with a total formula length not exceeding 8,192 characters.
- In Excel 2003 and lower, no more than 30 arguments are allowed, with a total length not exceeding 1,024 characters.
As an example of multiple AND conditions, please consider these ones:
- Amount (B2) should be greater than or equal to $100
- Order status (C2) is "Closed"
- Delivery date (D2) is within the current month
Now, we need an IF AND statement to identify the orders for which all 3 conditions are TRUE. And here it is:
=IF(AND(B2>=100, C2="Closed", MONTH(D2)=MONTH(TODAY())), "x", "")
Given that the 'current month' at the moment of writing was October, the formula delivers the below results:
Nested IF AND statements
When working with large worksheets, chances are that you may be required to check a few sets of different AND criteria at a time. For this, you take a classic Excel nested IF formula and extend its logical tests with AND statements, like this:
To get the general idea, please look at the following example.
Supposing you want to rate your service based on the shipment cost and estimated time of delivery (ETD):
- Excellent: shipment cost under $20 and ETD under 3 days
- Poor: shipment cost over $30 and ETD over 5 days
- Average: anything in between
To get it done, you write two individual IF AND statements:
IF(AND(B2<20, C2<3), "Excellent", …)
IF(AND(B2>30, C2>5), "Poor", …)
…and nest one into the other:
=IF(AND(B2>30, C2>5), "Poor", IF(AND(B2<20, C2<3), "Excellent", "Average"))
The result will look similar to this:
More formula examples can be found in Excel nested IF AND statements.
Case-sensitive IF AND function in Excel
As mentioned in the beginning of this tutorial, Excel IF AND formulas do not distinguish between uppercase and lowercase characters because the AND function is case-insensitive by nature.
If you are working with case-sensitive data and want to evaluate AND conditions taking into account the text case, do each individual logical test inside the EXACT function and nest those functions into your AND statement:
For this example, we are going to flag orders of a specific customer (e.g. the company named Cyberspace) with an amount exceeding a certain number, say $100.
As you can see in the below screenshot, some company names in column B look the same excerpt the characters case, and nevertheless they are different companies, so we have to check the names exactly. The amounts in column C are numbers, and we run a regular "greater than" test for them:
=IF(AND(EXACT(B2, "Cyberspace"), C2>100), "x", "")
To make the formula more flexible, you can input the target customer name and amount in two separate cells and refer to those cells. Just remember to lock the cell references with $ sign ($G$1 and $G$2 in our case) so they won't change when you copy the formula to other rows:
=IF(AND(EXACT(B2, $G$1), C2>$G$2), "x", "")
Now, you can type any name and amount in the referenced cells, and the formula will flag the corresponding orders in your table:
IF OR AND formula in Excel
In Excel IF formulas, you are not limited to using only one logical function. To check various combinations of multiple conditions, you are free to combine the IF, AND, OR and other functions to run the required logical tests. Here is an example of IF AND OR formula that tests a couple of OR conditions within AND. And now, I will show you how you can do two or more AND tests within the OR function.
Supposing, you wish to mark the orders of two customers with an amount greater than a certain number, say $100.
In the Excel language, our conditions are expressed in this way:
OR(AND(Customer1, Amount>100), AND(Customer2, Amount>100)
Assuming the customer names are in column B, amounts in column C, the 2 target names are in G1 and G2, and the target amount is in G3, you use this formula to mark the corresponding orders with "x":
=IF(OR(AND(B2=$G$1, C2>$G$3), AND(B2=$G$2, C2>$G$3)), "x", "")
The same results can be achieved with a more compact syntax:
=IF(AND(OR(B2=$G$1,B2= $G$2), C2>$G$3), "x", "")
Not sure you totally understand the formula's logic? More information can be found in Excel IF with multiple AND/OR conditions.
That's how you use the IF and AND functions together in Excel. Thank you for reading and see you next week!
Practice workbook
IF AND Excel – formula examples (.xlsx file)
490 comments
I have a list of 7 questions to be answered as a Y/N. I also have a table of the 7 questions (rows) and 4 risk levels (very low, low, medium, high) (columns). I need a cell at the bottom of the questions to say depending on what has been answered, what risk level it falls in. If it doesn't match Very Low, Low or Medium, it is High.
VL L M H
Q1 Y Y Y
Q2 Y Y N
Q3 Y N N
Q4 Y Y Y
Q5 Y Y Y
Q6 Y Y N
Q7 Y N N
This is my current formula and it works for Very Low and Low however not Medium and if it doesn't match the criteria for Very Low and Low, it should say High, but it doesn't.
=IF(AND(C3:C9=H3:H9),H2,IF(AND(C3:C9=I3:I9),I2,IF(AND(C3:C9-J3:J9),J2,K2)))
Can you help at all please?
Hi! I’m sorry but your description doesn’t give me a complete understanding of your task. I can guess what you need to fix in the formula: AND(C3:C9=J3:J9).
Maybe this article will be helpful: Compare two columns for matches and differences.
If this isn’t what you intended, please provide more detailed information about the issue.
Thank you for your response.
Essentially, the following formula works (with 2 x conditions)
=IF(AND(C4:C10=D4:D10),D3,IF(AND(C4:C10=E4:E10),E3,G3))
however it doesn't work with a 3rd condition shown on the formula below
=IF(AND(C4:C10=D4:D10),D3,IF(AND(C4:C10=E4:E10),E3,IF(AND(C4:C10-F4:F10),F3,G3)))
Gosh, just figured out why it wasn't working. There was a typo in the last condition. Have only been racking my brains for the past week.
Need some assistance in writing an excel formula for the below. I tried many of the suggestions without success.
If a cell has a value with either 1 or 2, populate PM; if the same cell has value 3 or 4, populate ME and if same cell has value 5, populate EE.
I know it's a greater than on less than, but my formula is still not working, even the nested is not working.
PM - score achieved 1-2 points
ME - score achieved 3-4 points
EE - score achieved 5 points
Values 1, 2, 3, 4 or 5 will be in the same cell.
Thank you for your assistance.
Hello Marcia!
You can find the answer to your question in this article: Excel Nested IF statement: examples, best practices and alternatives. Here are some variations of the formula:
=IF(A1>=5,"EE",IF(A1>=3,"ME","PM"))
=IFERROR(IFS(A1>=5,"EE",A1>=3,"ME",A1>=1,"PM"),"")
=IFERROR(CHOOSE(A1,"PM","PM","ME","ME","EE"),"")
I did try the nested if statement but it didn't work...maybe an error on my part.
Thank you so much for responding. I will try these.
The 1st option worked. Apparently the cell I was inputted the formula was corrupted...something within the cell was throwing an error. I deleted the rows and re-entered the formula and it worked. Thank you!!!
Hello, Can you please help with this formula =IF(AND(H3="Female",I3="Married",D3<=50,K3="Cat A"),Pricing!E2),IF(AND(H3="Female",I3="Married",D3<=50,K3="Cat B"),Pricing!E9) insurance premium does not change when it changes to "Cat B". Not sure why? Appreciate your help
Hi! Your formula contains a syntax error. Look for the example formulas here: Nested IF in Excel – formula with multiple conditions. For example:
=IF(AND(H3="Female",I3="Married",D3<=50,K3="Cat A"),Pricing!E2, IF(AND(H3="Female",I3="Married",D3<=50,K3="Cat B"),Pricing!E9))
I'm trying to track the progress of a job. I have 4 columns; each have a date entered when the task is completed. I have a 5th column reporting the status, but I can't get past the first status. When the first date is entered, it says "ready". But when the second, third, and fourth dates are entered, it doesn't change to "in progress", "finished", "complete". Is it possible to create this function?
Hello Karie!
If I understand your task correctly, this article may be helpful: Nested IF in Excel – formula with multiple conditions. The formula might look something like the following:
=IF(D1<>"","complete",IF(C1<>"","Finished",IF(B1<>"","in progress",IF(A1<>"","ready",""))))
Thank you!! This is exactly what I needed!
Hi,
I am racking my brain on what is wrong with this and I have tried several variations. I am trying to see if my value on my main sheet matches the 2 out of 3 values on three other sheets.
A2 is the cell I am checking and if present in 2/3 sheets I want it to be reported.
VF and SCI and 2 different sheets and the A2 value can be in either or of these but needs to be in one of them
CR is a submission sheet and A2 100% needs to be here.
I have tried the following:
=if(and(or(A2 ='VF'!A$2:A209,A2='CR'!E$2:E1209, A2='SCI'!A$2:A209)),A2, "Check ERROR") - states its correct but I know it should show up as an error
=if(and(or(A2='CR'!E$2:E1224), A2 ='VF!A$2:A224, A2='SCI'!A$2:A224),A2, "Check ERROR") - states error
=if(and(A7 ='Valley Fever Case Information'!A$2:A214,A7='Case Reports'!E$2:E1214),A7, if(and(A7='Salmonella Case Information'!A$2:A214,A7='Case Reports'!E$2:E1214),A7, "Check ERROR")) - states error.
Any help would be great
=
Hi! If I understand your task correctly, the following formula should work for you:
=IF(ISNUMBER(MATCH(2,(A2=vf!A$2:A209)+(A2=cr!E$2:E209)+(A2=sci!A$2:A209),0)),A2,"")
Use the MATCH function to find the position where the desired value is written on the 2 worksheets.
I hope it’ll be helpful.
Would you be able to tell from the below formula why this isn't working?
Right now, I am trying to pull data based on description (5 total | 5 rows) in column A and the month in row 38 starting in column B (Jan-Dec). Basically a 5 x 12 grid.
My formula is puling all is pulling all "None".
Are there limitations to the formula where it won't pull across row and columns?
=IF(AND($C$4:$C$32=$A39,$H$4:$H$32=B$38),"X","None")
Hi! Sorry, it's not quite clear what you are trying to achieve. Your description of the problem does not match the formula. From column C to column H and from row 4 to row 32 is not a 5 x 12 grid. Please clarify your specific problem or provide additional details to highlight exactly what you need.
Reached that point of frustration... Can't see what I'm doing wrong here :-(
This is trying to test for any of the words, 'Health, Performance or Conduct' being in N105,
AND
the number of comma-separated strings in V105 being either 'two or more' or otherwise 'one'.
=IF(AND
IF(OR(N105="Health",N105="Performance",N105="Conduct"),
IF((LEN(V105)-LEN(SUBSTITUTE(V105,",",""))+1)>1)),"Two+", "One")
Help greatly appreciated
Hi! I’m sorry, but your description doesn’t give me a complete understanding of your task. Maybe this article will be helpful: How to find substring in Excel. To give better advice, give an example of the value in N105 and the desired result.
Thank you, Alexander.
N105 will have one of the values of "Health", "Performance", "Conduct", "Suitability" or "Prohibited".
V105 will have one or more comma-separated free-text strings.
Some examples of the results I'm seeking:
1. N105 = "Health" and V105 = "Treatment program, Approved clinic, Education" returns result of "Two+"
2. N105 = "Health" and V105 = "Drug testing" returns result of "One"
3. N105 = "Conduct" and V105 = "Supervision, Education" returns result of "Two+"
4. N105 = "Prohibited" and V105 = "Suspended" returns result a FALSE result
I realise that I think I haven't resolved the FALSe part of the problem as in example 4.
Thanks for any help
Hi! I'm not sure if I understood you correctly. The description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=IF(AND(OR(N105="Health",N105="Performance",N105="Conduct"),
(LEN(V105)-LEN(SUBSTITUTE(V105,",",""))+1)>1),"Two+", IF(AND(OR(N105="Health",N105="Performance",N105="Conduct"),
(LEN(V105)-LEN(SUBSTITUTE(V105,",",""))+1)=1), "One"))
I recommend reading this guide: Nested IF with OR/AND conditions.
Thank you again, Alexander. That has done the trick! Really appreciate the help :-)
I have the following scenario and can't quite get all the nests. Hope you can help.
I need it to work like this:
IF Sheet1 B9:B20 match any of Sheet 2 B9:20 THEN add Sheet 2 K9:K20 and bring that value back to the cell with the formula
hi! Based on your description, it is hard to completely understand your task. I can assume that you can use the INDEX MATCH functions to find a value in a range. If this does not help, explain the problem in detail.
I am placing/writing a formula in cell C1
In A1 - Its EXPIRY DATE
In B1 - Its RENEWAL DATE
=IF(TODAY()>=B1,"SEND RENEW REMINDER","WAIT")
The above formula is working.
Now, I want to apply one more condition in same cell C1 - That if A1 is less than TODAY's date, the result should be "PENDING" and if I write manually (CANCELLED) in A1, the result should be "CANCELLED". Something like =IF(TODAY()<A1,"PENDING") =IF(A1=CANCELLED,"CANCELLED")
I am finding difficulty to apply the second condition merging with first condition in the same cell.
Please help me out.
Hi! To write multiple conditions in an IF formula, you can use this guide: Nested IF in Excel – formula with multiple conditions. For example:
=IF(TODAY()<A1,"PENDING", IF(TODAY()>=B1,"SEND RENEW REMINDER","WAIT"))
=IF(AND(F2="",G2=""),SUMIF(Budget!$B:$B,'One Pager'!$L$2,Budget!$U:$U),IF(F2="",SUMIF(Budget!$C:$C,'One Pager'!$L$2,Budget!$U:$U))),IF(AND(E2="",G2=""),SUMIF(Budget!$D:$D,'One Pager'!$L$2,Budget!$U:$U),IF(E2="",SUMIF(Budget!$E:$E,'One Pager'!$L$2,Budget!$U:$U))),IF(AND(D2="",G2=""),SUMIF(Budget!$F:$F,'One Pager'!$L$2,Budget!$U:$U),IF(D2="",SUMIF(Budget!$G:$G,'One Pager'!$L$2,Budget!$U:$U))),IF(AND(C2="Axis bank",G2=""),SUMIF(Budget!$H:$H,'One Pager'!$L$2,Budget!$U:$U),IF(C2="Axis bank",SUMIF(Budget!$I:$I,'One Pager'!$L$2,Budget!$U:$U)))
showing #value error
Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
I recommend reading this guide: #VALUE error in Excel: causes and fixes.
how to set an excel formula wherein,
i have a range between 5-11, if i input a value between 5-11, the 6% will appear automatically but if i input below or above the range, answer will be negative below or above 6%
Hi! Based on your description, it is hard to completely understand your task. Use these instructions to check a number within a specified interval: Excel IF: greater than AND less than. For example:
=IF(AND(A2>5, A2<11), 6%, "")
What is Wrong with this formula, it is giving ERROR:
IF(AND(N15="ACRE",P15="BIGHA”),M15*3.02500,IF(AND(N15="ACRE",P15="KATHA”),M15*60.50000,IF(AND(N15="KATHA",P15="ACRE”),M15*0.01653,IF(AND(N15="BIGHA",P15="ACRE”),M15*0.33058,IF(AND(N15="KATHA",P15="SQ.FT.”),M15*720.00003,IF(AND(N15="KATHA",P15="CHHATAK”),M15*16.00000,IF(AND(N15="CHHATAK",P15="KATHA”),M15*0.06250,IF(AND(N15="KATHA",P15="DECIMAL”),M15*1.65289256,IF(AND(N15="DECIMAL",P15="KATHA”),M15*0.60500,IF(AND(N15="ACRE",P15="SQ.FT.”,M15*43560,IF(AND(N15="SQ.FT.",P15="ACRE”,M1/43560)))))))))))
Hi! Check the quotation marks in your formula. Use " instead of ”
I made a grade monitoring sheet with 9 subjects I made 1 formula , I want to make a formula where the final grade will show in the table if I type the number or subject .For example 1 = SUBJECT 1.. this is my formula =IF(A1=1,W12) ..W12 is the final grade.. then in the next row I the formula I made is =IF(A1=2,W12)... it's shows the correct grade but the first column become FALSE.. what should I do?
Hi! Only one value can be written in cell A1. In this case, it is either 1 or 2. So one of your formulas will always return FALSE. Unfortunately, I don't quite understand what result you want to get. Maybe this article will be helpful: Nested IF in Excel – formula with multiple conditions.
Hello - I am trying to calculate increased or decreased spend YoY based on current performance. Here is the logic: (1) if rev is up and spend is down, increase spend by % growth in rev (2) if rev is down and spend is up, decrease spend by % decline in rev (3) if rev is up and spend is up, increase spend by % increase in rev (4) if rev and spend are flat, keep spend as is. The data inputs are across multiple sheets.
The tab names are (1) US YoY Spend % Change (2) US Rev YoY % Change (3) US Publisher Net Spend by Month
Here is the formula I'm inputting. I keep running into errors:
=IF(AND('US YoY Spend % Change'!$S$3>0%,'US Rev YoY % Change'!$S$3<0%),'US Publisher Net Spend by Month'!F3+(‘US Publisher Net Spend by Month'!F3*'US Rev YoY % Change'!S3),IF(AND('US YoY Spend % Change'!$S$30%),'US Publisher Net Spend by Month'!F3+(‘US Publisher Net Spend by Month'!F3*'US Rev YoY % Change'!S3),IF(AND('US YoY Spend % Change'!$S$3>0%,'US Rev YoY % Change'!$S$30>0%),'US Publisher Net Spend by Month'!F3+('US Publisher Net Spend by Month'!F3*'US Rev YoY % Change'!S3),IF(AND('US YoY Spend % Change'!$S$3=0%,'US Rev YoY % Change'!$S$30=0%),'US Publisher Net Spend by Month'!F3, ‘US Publisher Net Spend by Month'!F3))))
Looking for some guidance please..
I have 3 IF formulas - all functioning as i need them to in separate cells. however i am struggling to nest them. For context i am creating a diary that will flag up dates for various conditions.
the 3 formulas are as follows :
1- =IF(H2>=TODAY()+8,"OVER 7 DAYS", "")
2- =IF(AND(H2>=TODAY()+1,H2<=TODAY()+7),"WITHIN 7 DAYS","")
3- =IF(H2=TODAY()+8),"OVER 7 DAYS", IF(AND(H2>=TODAY()+1),(H2<=TODAY()+7)),"WITHIN 7 DAYS", IF((H2=TODAY()+8),"OVER A WEEK", (H2>=TODAY()+1 & H2<=TODAY()+7), "WITHIN 7 DAYS" , (H2<=TODAY()), "TODAY / OVERDUE", "")
Thanks in advance for any guidance on why i'm not getting anywhere !
The error is too many functions is there a way around this ?
Hello! If you have too many conditions in your formula, I recommend using the IFS function instead of the nested IF. Based on the information given, the formula could be as follows:
=IFS(H2>=TODAY()+8, "OVER 7 DAYS", AND(H2>=TODAY()+1,H2<=TODAY()+7), "WITHIN 7 DAYS", H2=TODAY()+8,"OVER 7 DAYS", H2=TODAY()+8,"OVER A WEEK", H2<=TODAY(), "TODAY / OVERDUE")
You can find the examples and detailed instructions here: The new Excel IFS function instead of multiple IF.
=IF(AND(C5>120,160<D5<200,1.59<E5120,160<D5<200,1.59<E5120,160<D5<200,1.59<E5120,160<D5<200,1.59<E5120,201<D5<280,4.8<E5120,201<D5<280,4.8<E5120,201<D5<280,4.8<E5120,201<D5<280,4.8<E5120,D5>280,15.6<E5120,D5>280,15.6<E5120,D5>280,15.6<E5120,D5>280,15.6<E5<35.4,F5="Boulder"),1000,""))))))))))))
What is wrong with this that it cant give me an out put?
Hi! Your formula contained errors. Leave only one parenthesis at the end of the formula.
Strength(mg) Quantity
250mg 1 tablet
500mg 2 tablets
750mg 3 tablets
1000mg 4 tablets
1250mg 5 tablets
1500mg 6 tablets
1750mg 7 tablets
2000mg 8 tablets
2250mg 9 tablets
2500mg 10 tablets
How do i set a cell that when i enter any mg in D26, it should tell me the value of tablets depends in the tablet. for eg: 251mg, it must input 2 tablets and 249mg, it must input 1 tablet.
Thank you.
Hi! To find the first value in the table that is greater than the criterion, use the XLOOKUP function
=XLOOKUP(D1,A1:A10,B1:B10,,1)
You can also use the INDEX MATCH functions:
=INDEX(B1:B10, MATCH(TRUE, INDEX(A1:A10 >= D1, 0), ))
Note that column A must contain numbers, not text. That is, 250, not 250mg.
I am trying to check if a state and email address are the same across two different data sets. Eg email @email.com and Arizona are the matching in both lists. Any help is welcome
Hi! Try to use the recommendations described in this article: Compare two columns for matches and differences. If this is not what you wanted, please describe the problem in more detail.
hi , can help what the best formula i can use to solve my case.
i have 2 sheet tab :
1st sheet : weekly sales WK 1, WK 2, WK 3
2ns sheet : daily sales with date and week (WK 1, WK 2, WK 3)
how can i build formula , if WK 1 (weekly sales) = WK 1 (daily sales) , then qty (weekly sales) /7 but if WK 1 (weekly sales) WK 1 (daily Sales) , WK 1 weekly sales need to move WK 2 (WK1+1) and calculate WK2 / 7
Hi! Unfortunately, the conditions you describe are unclear. Please provide me with an example of the source data and the expected result.
15 days volumes/ Two
way distance in Kms 0 -1500 1501 - 2250 2215-3000 3001, - 3750 >3750
0-50 2.34 1.70 1.17 1.05 1.05
50-100 6.50 3.20 2.90 1.87 1.30
100-150 11.00 4.68 4.00 3.00 2.34
150+ 12.00 11.00 7.90 5.00 3.00
Kindly solve this issue
Unfortunately, this information is not enough to understand what you need.