Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure which formula to use..
Hello all... not sure if this is even possible to do in Excel but I
have a worksheet where I am trying to calculate an answer using multiple conditions. Column A has years of service (1, 5, 10, etc etc etc). Column B has hours per week (35 or 37.5 depending on where a person works.) Column C will be the result of the formula. What I want to do is have a formula calculate an entitlement of time based on that first column AND second column which could contain a wide range of information. ie. if column A equals a number between 1 and 3, and column B equals 35, then multiply the resulting number by "this amount"..... BUT if column A equals a number between 4 and 10, and column B equals 35, then multiply the resulting number by "that amount"..... For Example ((Column A=2, Column B=35), therefore the formula would look at column A, verify that it is between 1 and 3, look at column B verify that it is 35, and then multiply the result by say the number "5")) but... If column A=5, Column B=35, then the formula would look at column A, see that it equals the second condition ((between 4 and 10)), now verify against column B and multiply the result by say the number "8". Initially I had thought of the "If/Then" function, but there are multiple conditions because it's really "If" this, AND this, then do "that" but "If" That, AND that, then do "This".... I can't figure out what formula almost combines the "If/then" function and allows a mathematical calculation to be attached to the If/Then condition. Oh... I need to calculate this for almost 1500 employees so that's the reason I don't want to do this by hand...... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure which formula to use..
Thanks for your help...
I was able to figure out the first part of the formula using the =IF statement. So in my original example, I successfully can get Excel to calculate "this number" and "That number" (Your formula below gave me some insight as to how to clean up my formula (I did not know about using A1=1 (literal translation of "If A1 is greater than or less than 1".... ) Based on what you wrote as the formula and what I (think) I want to translate it to, here's what I came up with: =IF(AND(F4<4,C4=35),5.85*2),IF(AND(F4=4,F4<=10,C4 =35),8.75*2),IF(AND(F410,C4=35),11.67*2) Literal translation: If cell F4 is less than "4" and cell C4=35, then Multiply 5.85*2 but If cell F4 is greater than or equal to "4" and cell F4 is less than or equal to "10" and cell C4=35, then Multiply 8.75*2 but If cell F4 is greater than 10 and cell C4=35, then Multiply 11.67*2 Of course my formula is not working and I'm getting the #Value! error.I could very well just be missing a ( ) somewhere, but I'm not sure... shail wrote: You didn't specified what is "that number" or "this number". You can write the formula as =if(and(a1=1,a1<=3,b1=35),resulting number*this number,if(and(a1=4,a1<=10,b1=35),resulting number*this number,if(..............))) so on.. If you could specify what are resulting number and this number it would be easier for me to write a formula for you. Thanks Shail gmail.com wrote: Hello all... not sure if this is even possible to do in Excel but I have a worksheet where I am trying to calculate an answer using multiple conditions. Column A has years of service (1, 5, 10, etc etc etc). Column B has hours per week (35 or 37.5 depending on where a person works.) Column C will be the result of the formula. What I want to do is have a formula calculate an entitlement of time based on that first column AND second column which could contain a wide range of information. ie. if column A equals a number between 1 and 3, and column B equals 35, then multiply the resulting number by "this amount"..... BUT if column A equals a number between 4 and 10, and column B equals 35, then multiply the resulting number by "that amount"..... For Example ((Column A=2, Column B=35), therefore the formula would look at column A, verify that it is between 1 and 3, look at column B verify that it is 35, and then multiply the result by say the number "5")) but... If column A=5, Column B=35, then the formula would look at column A, see that it equals the second condition ((between 4 and 10)), now verify against column B and multiply the result by say the number "8". Initially I had thought of the "If/Then" function, but there are multiple conditions because it's really "If" this, AND this, then do "that" but "If" That, AND that, then do "This".... I can't figure out what formula almost combines the "If/then" function and allows a mathematical calculation to be attached to the If/Then condition. Oh... I need to calculate this for almost 1500 employees so that's the reason I don't want to do this by hand...... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure which formula to use..
Hi Eric,
You should rewrite the formula as =IF(AND(F4<4,C4=35),5.85*2,IF(AND(F4=4,F4<=10,C4= 35),8.75*2,IF(AND(F410,C4=35),11.67*2,0))) This will help you. thanks, Shail Eric wrote: Thanks for your help... I was able to figure out the first part of the formula using the =IF statement. So in my original example, I successfully can get Excel to calculate "this number" and "That number" (Your formula below gave me some insight as to how to clean up my formula (I did not know about using A1=1 (literal translation of "If A1 is greater than or less than 1".... ) Based on what you wrote as the formula and what I (think) I want to translate it to, here's what I came up with: =IF(AND(F4<4,C4=35),5.85*2),IF(AND(F4=4,F4<=10,C4 =35),8.75*2),IF(AND(F410,C4=35),11.67*2) Literal translation: If cell F4 is less than "4" and cell C4=35, then Multiply 5.85*2 but If cell F4 is greater than or equal to "4" and cell F4 is less than or equal to "10" and cell C4=35, then Multiply 8.75*2 but If cell F4 is greater than 10 and cell C4=35, then Multiply 11.67*2 Of course my formula is not working and I'm getting the #Value! error.I could very well just be missing a ( ) somewhere, but I'm not sure... shail wrote: You didn't specified what is "that number" or "this number". You can write the formula as =if(and(a1=1,a1<=3,b1=35),resulting number*this number,if(and(a1=4,a1<=10,b1=35),resulting number*this number,if(..............))) so on.. If you could specify what are resulting number and this number it would be easier for me to write a formula for you. Thanks Shail gmail.com wrote: Hello all... not sure if this is even possible to do in Excel but I have a worksheet where I am trying to calculate an answer using multiple conditions. Column A has years of service (1, 5, 10, etc etc etc). Column B has hours per week (35 or 37.5 depending on where a person works.) Column C will be the result of the formula. What I want to do is have a formula calculate an entitlement of time based on that first column AND second column which could contain a wide range of information. ie. if column A equals a number between 1 and 3, and column B equals 35, then multiply the resulting number by "this amount"..... BUT if column A equals a number between 4 and 10, and column B equals 35, then multiply the resulting number by "that amount"..... For Example ((Column A=2, Column B=35), therefore the formula would look at column A, verify that it is between 1 and 3, look at column B verify that it is 35, and then multiply the result by say the number "5")) but... If column A=5, Column B=35, then the formula would look at column A, see that it equals the second condition ((between 4 and 10)), now verify against column B and multiply the result by say the number "8". Initially I had thought of the "If/Then" function, but there are multiple conditions because it's really "If" this, AND this, then do "that" but "If" That, AND that, then do "This".... I can't figure out what formula almost combines the "If/then" function and allows a mathematical calculation to be attached to the If/Then condition. Oh... I need to calculate this for almost 1500 employees so that's the reason I don't want to do this by hand...... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure which formula to use..
Hi Eric,
You should rewrite the formula as =IF(AND(F4<4,C4=35),5.85*2,IF(AND(F4=4,F4<=10,C4= 35),8.75*2,IF(AND(F410,C4=35),11.67*2,0))) This will help you. thanks, Shail Eric wrote: Thanks for your help... I was able to figure out the first part of the formula using the =IF statement. So in my original example, I successfully can get Excel to calculate "this number" and "That number" (Your formula below gave me some insight as to how to clean up my formula (I did not know about using A1=1 (literal translation of "If A1 is greater than or less than 1".... ) Based on what you wrote as the formula and what I (think) I want to translate it to, here's what I came up with: =IF(AND(F4<4,C4=35),5.85*2),IF(AND(F4=4,F4<=10,C4 =35),8.75*2),IF(AND(F410,C4=35),11.67*2) Literal translation: If cell F4 is less than "4" and cell C4=35, then Multiply 5.85*2 but If cell F4 is greater than or equal to "4" and cell F4 is less than or equal to "10" and cell C4=35, then Multiply 8.75*2 but If cell F4 is greater than 10 and cell C4=35, then Multiply 11.67*2 Of course my formula is not working and I'm getting the #Value! error.I could very well just be missing a ( ) somewhere, but I'm not sure... shail wrote: You didn't specified what is "that number" or "this number". You can write the formula as =if(and(a1=1,a1<=3,b1=35),resulting number*this number,if(and(a1=4,a1<=10,b1=35),resulting number*this number,if(..............))) so on.. If you could specify what are resulting number and this number it would be easier for me to write a formula for you. Thanks Shail gmail.com wrote: Hello all... not sure if this is even possible to do in Excel but I have a worksheet where I am trying to calculate an answer using multiple conditions. Column A has years of service (1, 5, 10, etc etc etc). Column B has hours per week (35 or 37.5 depending on where a person works.) Column C will be the result of the formula. What I want to do is have a formula calculate an entitlement of time based on that first column AND second column which could contain a wide range of information. ie. if column A equals a number between 1 and 3, and column B equals 35, then multiply the resulting number by "this amount"..... BUT if column A equals a number between 4 and 10, and column B equals 35, then multiply the resulting number by "that amount"..... For Example ((Column A=2, Column B=35), therefore the formula would look at column A, verify that it is between 1 and 3, look at column B verify that it is 35, and then multiply the result by say the number "5")) but... If column A=5, Column B=35, then the formula would look at column A, see that it equals the second condition ((between 4 and 10)), now verify against column B and multiply the result by say the number "8". Initially I had thought of the "If/Then" function, but there are multiple conditions because it's really "If" this, AND this, then do "that" but "If" That, AND that, then do "This".... I can't figure out what formula almost combines the "If/then" function and allows a mathematical calculation to be attached to the If/Then condition. Oh... I need to calculate this for almost 1500 employees so that's the reason I don't want to do this by hand...... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |