| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function -Help Please!!
hi,
i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
Try
=(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7 )+(D99=305)*(D7=Sheet2!J7) I'll let you fix the worksheet name! Note: you will get 0 if D99 is none of required values and/or if D7 fits none or the required values best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ups.com... hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
=if($D$99=1960,if($D$7='Proficy CL Limits,
Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits, Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits, Ranges"!$J$7,1,0),"otherwise"))) " wrote: hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
On May 1, 11:28 am, "Bernard Liengme"
wrote: Try =(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7 )+(D99=305)*(D7=Sheet2!J7*) I'll let you fix the worksheet name! Note: you will get 0 if D99 is none of required values and/or if D7 fits none or the required values best wishes -- Bernard V Liengmewww.stfx.ca/people/bliengme remove caps from email wrote in message ups.com... hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated- Hide quoted text - - Show quoted text - If see how you are having it check the value of D99 but if the value of D99 = 1960 then the formula that must be in cell D100 should be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) That is the part i am stuck on |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
I just noticed I put double quotes rather than single quotes in my equation
if D99 will only be 1960,311 or 305 you could also use =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"} )&"$7"),1,0) Ranges"!$G$7,1,0 "bj" wrote: =if($D$99=1960,if($D$7='Proficy CL Limits, Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits, Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits, Ranges"!$J$7,1,0),"otherwise"))) " wrote: hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
On May 1, 12:57 pm, bj wrote:
I just noticed I put double quotes rather than single quotes in my equation if D99 will only be 1960,311 or 305 you could also use =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"} )&"$7"),1,0) Ranges"!$G$7,1,0 "bj" wrote: =if($D$99=1960,if($D$7='Proficy CL Limits, Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits, Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits, Ranges"!$J$7,1,0),"otherwise"))) " wrote: hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated- Hide quoted text - - Show quoted text - It is still not returning correctly... it seems a problem here (D$D99, if i change that to D99 i get #N/A back in the field any ideas? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
In the formula
=(D99=1960)*(D7='Proficy CL Limits Ranges'!G7) +(D99=311)*(D7='Proficy CL Limits Ranges'!D7) +(D99=305)*(D7='Proficy CL Limits Ranges'!J7*) each term (bla-bla) will either be true of false; when you multiple TRUE?FALSE in Excel they get treated as 1/0. SO each term can be considered 1 or 0 so if D99 does equal 1960 and D7 does equal what is in G7 on the other sheet then we get 1*1 + 0*0 +0*0 which = 1 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ps.com... On May 1, 11:28 am, "Bernard Liengme" wrote: Try =(D99=1960)*(D7=Sheet2!G7)+(D99=311)*(D7=Sheet2!D7 )+(D99=305)*(D7=Sheet2!J7*) I'll let you fix the worksheet name! Note: you will get 0 if D99 is none of required values and/or if D7 fits none or the required values best wishes -- Bernard V Liengmewww.stfx.ca/people/bliengme remove caps from email wrote in message ups.com... hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated- Hide quoted text - - Show quoted text - If see how you are having it check the value of D99 but if the value of D99 = 1960 then the formula that must be in cell D100 should be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) That is the part i am stuck on |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
" wrote: On May 1, 12:57 pm, bj wrote: I just noticed I put double quotes rather than single quotes in my equation if D99 will only be 1960,311 or 305 you could also use =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"} )&"$7"),1,0) Ranges"!$G$7,1,0 "bj" wrote: =if($D$99=1960,if($D$7='Proficy CL Limits, Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits, Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits, Ranges"!$J$7,1,0),"otherwise"))) " wrote: hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated- Hide quoted text - - Show quoted text - It is still not returning correctly... it seems a problem here (D$D99, if i change that to D99 i get #N/A back in the field any ideas? thats cause I had a second error =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D99,{305,311,1960},{"J","D","G"} )&"$7"),1,0) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function -Help Please!!
On May 2, 2:08 pm, bj wrote:
" wrote: On May 1, 12:57 pm, bj wrote: I just noticed I put double quotes rather than single quotes in my equation if D99 will only be 1960,311 or 305 you could also use =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D$D99,{305,311,1960;"J","D","G"} )&"$7"),1,0) Ranges"!$G$7,1,0 "bj" wrote: =if($D$99=1960,if($D$7='Proficy CL Limits, Ranges"!$G$7,1,0),if($D$99=311,if($D$7='Proficy CL Limits, Ranges"!$D$7,1,0),if($D$99=305,if($D$7='Proficy CL Limits, Ranges"!$J$7,1,0),"otherwise"))) " wrote: hi, i need some help with a complex excel function that may need to be a macro but i am not the most proficent at them. what i need is below if anyone can offer help i need to check the data in D99 and then run a specific formula depending on the data in D99 if d99 = 1960 then the forumula needs to be =IF(D7='Proficy CL Limits, Ranges'!G7,1,0) if d99 = 311 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!D7,1,0) if d99 = 305 then the formula needs to be =IF(D7='Proficy CL Limits, Ranges'!J7,1,0) the formula must then run in d100 to d184 Any help would be greatly appreciated- Hide quoted text - - Show quoted text - It is still not returning correctly... it seems a problem here (D$D99, if i change that to D99 i get #N/A back in the field any ideas? thats cause I had a second error =if(D7=indirect("'Proficy CL Limits, Ranges'!$"&lookup(D99,{305,311,1960},{"J","D","G"} )&"$7"),1,0)- Hide quoted text - - Show quoted text - I am still getting #N/A with =IF(D7=INDIRECT("'Proficy CL Limits, Ranges'!$"&LOOKUP(D99,{305,311,1960},{"J","D","G"} )&"$7"),1,0) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| challenge! javascript function into excel function | Excel Worksheet Functions | |||
| FUNCTION GETPIVOTDATA MICROSOFT EXCEL 2003 VS EXCEL 2004 FOR MAC | Excel Worksheet Functions | |||
| Excel Workday Function with another function | Excel Discussion (Misc queries) | |||
| Can you nest a MID function within a IF function in Excel | Excel Worksheet Functions | |||
| Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions | |||
Linear Mode
