| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating
What formula to use if you want to make reference to 2 cells;
example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
I am sure there is a way. What kind of information is in A1? Is it a date
or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
the month and year is A1 . I was a formula to say if A1 equal to B10 1( in
this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
If both A1 and B10 are in date formats try this formula. Change the values
you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
Thanks Jacob,
I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
Do you mean
=IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
I want the cell to be the maximum 1 ( meaning it could be less than 1)
"Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
For example in the below if month(A1) and month(B1) has a difference of 5
what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
I say your other post...Is this what you are looking for ?
=IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
It is still giving me error message; any other suggestion?
"Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
Do you mean...
=IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: It is still giving me error message; any other suggestion? "Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
maybe I am not explaining correctly. The formula is not working. I am
trying to copy what I have and paste it in the message box, but it is not pasting correctly. "Jacob Skaria" wrote: Do you mean... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: It is still giving me error message; any other suggestion? "Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
I will explain what the formula does and from there you should be able to
take up... The IF condition in the formula If( a condition, value to return if condition is true, value to return if condition is false) Month is a function to return month from a date cell So here we are comparing the month of B2 with that of D10. If both are same we are returning the value as 1 which I think is what you require. If the month is not same then we are taking the minimum of 1 and d11+d12+d13. I am not sure what this calculation is . If you look at your previous replies and even your original query you have not mentioned the condition at all. I got this D11+D12-D13 from your other post... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) If this post helps click Yes --------------- Jacob Skaria "Link" wrote: maybe I am not explaining correctly. The formula is not working. I am trying to copy what I have and paste it in the message box, but it is not pasting correctly. "Jacob Skaria" wrote: Do you mean... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: It is still giving me error message; any other suggestion? "Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
Jacob thank for your help! I put bracket beside your question what the calculation I want. "Jacob Skaria" wrote: I will explain what the formula does and from there you should be able to take up... The IF condition in the formula If( a condition, value to return if condition is true, value to return if condition is false) Month is a function to return month from a date cell So here we are comparing the month of B2 with that of D10. If both are same we are returning the value as 1 which I think is what you require. ( IF THE RESULTS AFTER CALCULATING D11+D12-D13 IS 1 OR NEGATIVES IT STAYS; IF THE RESULT IS OVER 1 IT RETURNS A VALUE OF 1) If the month is not same then we are taking the minimum of 1 and d11+d12+d13. I am not sure what this calculation is . If you look at your previous replies and even your original query you have not mentioned the condition at all. I got this D11+D12-D13 from your other post...(IF THE MONTHS ARE NOT THE SAME, THE RESULT FROM CALCULATING D11+D12-D13 IF OK) =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) If this post helps click Yes --------------- Jacob Skaria "Link" wrote: maybe I am not explaining correctly. The formula is not working. I am trying to copy what I have and paste it in the message box, but it is not pasting correctly. "Jacob Skaria" wrote: Do you mean... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: It is still giving me error message; any other suggestion? "Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
calculating
Jacob, I typed below in bracket next to the questions you asked. I am trying
hard to explain. Month is a function to return month from a date cell So here we are comparing the month of B2 with that of D10. If both are same we are returning the value as 1 which I think is what you require. ( IF THE RESULTS AFTER CALCULATING D11+D12-D13 IS 1 OR NEGATIVES IT STAYS; IF THE RESULT IS OVER 1 IT RETURNS A VALUE OF 1) If the month is not same then we are taking the minimum of 1 and d11+d12+d13. I am not sure what this calculation is . If you look at your previous replies and even your original query you have not mentioned the condition at all. I got this D11+D12-D13 from your other post...(IF THE MONTHS ARE NOT THE SAME, THE RESULT FROM CALCULATING D11+D12-D13 IF OK) "Jacob Skaria" wrote: I will explain what the formula does and from there you should be able to take up... The IF condition in the formula If( a condition, value to return if condition is true, value to return if condition is false) Month is a function to return month from a date cell So here we are comparing the month of B2 with that of D10. If both are same we are returning the value as 1 which I think is what you require. If the month is not same then we are taking the minimum of 1 and d11+d12+d13. I am not sure what this calculation is . If you look at your previous replies and even your original query you have not mentioned the condition at all. I got this D11+D12-D13 from your other post... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) If this post helps click Yes --------------- Jacob Skaria "Link" wrote: maybe I am not explaining correctly. The formula is not working. I am trying to copy what I have and paste it in the message box, but it is not pasting correctly. "Jacob Skaria" wrote: Do you mean... =IF(Month(B2)=Month(D10),1,MIN(1,D11+D12-D13)) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: It is still giving me error message; any other suggestion? "Jacob Skaria" wrote: I say your other post...Is this what you are looking for ? =IF(Month(B2)=Month(D10),1,D11+D12-D13) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For example in the below if month(A1) and month(B1) has a difference of 5 what is expected. =IF(Month(A1)=Month(B10),1,0) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: I want the cell to be the maximum 1 ( meaning it could be less than 1) "Jacob Skaria" wrote: Do you mean =IF(Month(A1)=Month(B10),1,0) If not post back with what value you are expecting..if the month is not same.. If this post helps click Yes --------------- Jacob Skaria "Link" wrote: Thanks Jacob, I want it to be either 1 or less than 1 (the maximum should be 1 ) "Jacob Skaria" wrote: If both A1 and B10 are in date formats try this formula. Change the values you want accordingly//In the below example if both months are same it will return 1 and if not 2. Adjust as required. =IF(Month(A1)=Month(B10),1,2) -- If this post helps click Yes --------------- Jacob Skaria "Link" wrote: the month and year is A1 . I was a formula to say if A1 equal to B10 1( in this case if A1 and B10 are the same month it should be 1 or less. "PJFry" wrote: I am sure there is a way. What kind of information is in A1? Is it a date or just the year? Is B10 always going to be the cell you compare too? And what do you want to do if the A1<B10? -- Regards, PJ Please rate this post using the vote buttons if it was helpful. "Link" wrote: What formula to use if you want to make reference to 2 cells; example: If year A1 equal to B10 the maximum value it can have is 2 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| not calculating zero | Excel Discussion (Misc queries) | |||
| Not Calculating | Excel Worksheet Functions | |||
| Calculating Age | Excel Worksheet Functions | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
| Not Calculating? | Excel Worksheet Functions | |||
![Link[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
