Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a way
Ok. I have a formula that is too long to put in one cell. So it has been
spread down 10 or so cell's then used =SUM(N3:N300) to add them together (right down the bottom of the spreadsheet hidden away). Im sure there is a way to get this smaller by using a relative range rather than a different formula for each row (and not have huge tables at the bottom doing all the working, just keep it to the one cell)... I need it to read 2 separate columns data only horizontally across the rows, but over a range of 300 rows. Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities.The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. So, came up with this.. =IF(N3+AC3=2,1,0) described as.. (If column N (Nik) + column AC (Auckland) = 2, add 1 to this cell (the TOTAL which means Nik made a sale in Auckland), if it doesnt equal 2 then add nothing, no sale... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each.. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0 )+IF(N6+AC6=2,1,0)+IF(N7+AC7=2,1,0) +IF(N8+AC8=2,1,0)+IF(N9+AC9=2,1,0)+IF(N10+AC10=2,1 ,0)+IF(N11+AC11=2,1,0)+IF(N12+AC12=2,1,0) +IF(N13+AC13=2,1,0)+IF(N14+AC14=2,1,0)+IF(N15+AC15 =2,1,0)+IF(N16+AC16=2,1,0)+IF(N17+AC17=2,1,0) +IF(N18+AC18=2,1,0)+IF(N19+AC19=2,1,0)+IF(N20+AC20 =2,1,0)+IF(N21+AC21=2,1,0)+IF(N22+AC22=2,1,0) +IF(N23+AC23=2,1,0)+IF(N24+AC24=2,1,0)+IF(N25+AC25 =2,1,0)+IF(N26+AC26=2,1,0)+IF(N27+AC27=2,1,0) +IF(N28+AC28=2,1,0)+IF(N29+AC29=2,1,0)+IF(N30+AC30 =2,1,0)+IF(N31+AC31=2,1,0)+IF(N32+AC32=2,1,0) +IF(N33+AC33=2,1,0)+IF(N34+AC34=2,1,0)+IF(N35+AC35 =2,1,0)+IF(N36+AC36=2,1,0)+IF(N37+AC37=2,1,0) +IF(N38+AC38=2,1,0)+IF(N39+AC39=2,1,0)+IF(N40+AC40 =2,1,0)+IF(N41+AC41=2,1,0)+IF(N42+AC42=2,1,0) +IF(N43+AC43=2,1,0)+IF(N44+AC44=2,1,0)+IF(N45+AC45 =2,1,0)+IF(N46+AC46=2,1,0)+IF(N47+AC47=2,1,0) +IF(N48+AC48=2,1,0)+IF(N49+AC49=2,1,0)+IF(N50+AC50 =2,1,0)+IF(N51+AC51=2,1,0)+IF(N52+AC52=2,1,0) +IF(N53+AC53=2,1,0)+IF(N54+AC54=2,1,0)+IF(N55+AC55 =2,1,0)+IF(N56+AC56=2,1,0)+IF(N57+AC57=2,1,0) +IF(N58+AC58=2,1,0)+IF(N59+AC59=2,1,0)+IF(N60+AC60 =2,1,0)+IF(N61+AC61=2,1,0)+IF(N62+AC62=2,1,0) +IF(N63+AC63=2,1,0)+IF(N64+AC64=2,1,0)+IF(N65+AC65 =2,1,0)+IF(N66+AC66=2,1,0)+IF(N67+AC67=2,1,0) +IF(N68+AC68=2,1,0)+IF(N69+AC69=2,1,0)+IF(N70+AC70 =2,1,0)+IF(N71+AC71=2,1,0)+IF(N72+AC72=2,1,0) +IF(N73+AC73=2,1,0)+IF(N74+AC74=2,1,0)+IF(N75+AC75 =2,1,0)+IF(N76+AC76=2,1,0)+IF(N77+AC77=2,1,0) +IF(N78+AC78=2,1,0)+IF(N79+AC79=2,1,0)+IF(N80+AC80 =2,1,0)+IF(N81+AC81=2,1,0)+IF(N82+AC82=2,1,0) +IF(N83+AC83=2,1,0)+IF(N84+AC84=2,1,0)+IF(N85+AC85 =2,1,0)+IF(N86+AC86=2,1,0)+IF(N87+AC87=2,1,0) +IF(N88+AC88=2,1,0)+IF(N89+AC89=2,1,0)+IF(N90+AC90 =2,1,0)+IF(N91+AC91=2,1,0)+IF(N92+AC92=2,1,0) +IF(N93+AC93=2,1,0)+IF(N94+AC94=2,1,0)+IF(N95+AC95 =2,1,0)+IF(N96+AC96=2,1,0)+IF(N97+AC97=2,1,0) +IF(N98+AC98=2,1,0)+IF(N99+AC99=2,1,0)+IF(N100+AC1 00=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102+AC102=2,1,0 ) And that only covers Nik - Auckland, up to row 102. And is spread out over many cells, then summed as described earlier. Is there a way i can range this? Id want it to be something like =IF(N3:N300+AC3:AC300=2,1,0) so it does the same thing but covers all the rows, in one cell.. But that formula doesnt work... Any ideas? Im using Excel 2002. Yes, old school. Any help appriciated. Cheers. Nik. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a way
N|X6S|X wrote...
.... Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities. The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. . . . .... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1, 0) .... IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102 +AC102=2,1,0) .... For all 4 cities at once, use =SUMPRODUCT(--(N3:N102+AC3:AF102=2)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a way
A quick and dirty way to do it:
=SUMPRODUCT( --( N$3:N$300 + $AC$3:$AF$300 = 2 ) ) But if your data contains non numeric values, it will fail... -- Regards, Luc. "Festina Lente" "N|X6S|X" wrote: Ok. I have a formula that is too long to put in one cell. So it has been spread down 10 or so cell's then used =SUM(N3:N300) to add them together (right down the bottom of the spreadsheet hidden away). Im sure there is a way to get this smaller by using a relative range rather than a different formula for each row (and not have huge tables at the bottom doing all the working, just keep it to the one cell)... I need it to read 2 separate columns data only horizontally across the rows, but over a range of 300 rows. Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities.The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. So, came up with this.. =IF(N3+AC3=2,1,0) described as.. (If column N (Nik) + column AC (Auckland) = 2, add 1 to this cell (the TOTAL which means Nik made a sale in Auckland), if it doesnt equal 2 then add nothing, no sale... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each.. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0 )+IF(N6+AC6=2,1,0)+IF(N7+AC7=2,1,0) +IF(N8+AC8=2,1,0)+IF(N9+AC9=2,1,0)+IF(N10+AC10=2,1 ,0)+IF(N11+AC11=2,1,0)+IF(N12+AC12=2,1,0) +IF(N13+AC13=2,1,0)+IF(N14+AC14=2,1,0)+IF(N15+AC15 =2,1,0)+IF(N16+AC16=2,1,0)+IF(N17+AC17=2,1,0) +IF(N18+AC18=2,1,0)+IF(N19+AC19=2,1,0)+IF(N20+AC20 =2,1,0)+IF(N21+AC21=2,1,0)+IF(N22+AC22=2,1,0) +IF(N23+AC23=2,1,0)+IF(N24+AC24=2,1,0)+IF(N25+AC25 =2,1,0)+IF(N26+AC26=2,1,0)+IF(N27+AC27=2,1,0) +IF(N28+AC28=2,1,0)+IF(N29+AC29=2,1,0)+IF(N30+AC30 =2,1,0)+IF(N31+AC31=2,1,0)+IF(N32+AC32=2,1,0) +IF(N33+AC33=2,1,0)+IF(N34+AC34=2,1,0)+IF(N35+AC35 =2,1,0)+IF(N36+AC36=2,1,0)+IF(N37+AC37=2,1,0) +IF(N38+AC38=2,1,0)+IF(N39+AC39=2,1,0)+IF(N40+AC40 =2,1,0)+IF(N41+AC41=2,1,0)+IF(N42+AC42=2,1,0) +IF(N43+AC43=2,1,0)+IF(N44+AC44=2,1,0)+IF(N45+AC45 =2,1,0)+IF(N46+AC46=2,1,0)+IF(N47+AC47=2,1,0) +IF(N48+AC48=2,1,0)+IF(N49+AC49=2,1,0)+IF(N50+AC50 =2,1,0)+IF(N51+AC51=2,1,0)+IF(N52+AC52=2,1,0) +IF(N53+AC53=2,1,0)+IF(N54+AC54=2,1,0)+IF(N55+AC55 =2,1,0)+IF(N56+AC56=2,1,0)+IF(N57+AC57=2,1,0) +IF(N58+AC58=2,1,0)+IF(N59+AC59=2,1,0)+IF(N60+AC60 =2,1,0)+IF(N61+AC61=2,1,0)+IF(N62+AC62=2,1,0) +IF(N63+AC63=2,1,0)+IF(N64+AC64=2,1,0)+IF(N65+AC65 =2,1,0)+IF(N66+AC66=2,1,0)+IF(N67+AC67=2,1,0) +IF(N68+AC68=2,1,0)+IF(N69+AC69=2,1,0)+IF(N70+AC70 =2,1,0)+IF(N71+AC71=2,1,0)+IF(N72+AC72=2,1,0) +IF(N73+AC73=2,1,0)+IF(N74+AC74=2,1,0)+IF(N75+AC75 =2,1,0)+IF(N76+AC76=2,1,0)+IF(N77+AC77=2,1,0) +IF(N78+AC78=2,1,0)+IF(N79+AC79=2,1,0)+IF(N80+AC80 =2,1,0)+IF(N81+AC81=2,1,0)+IF(N82+AC82=2,1,0) +IF(N83+AC83=2,1,0)+IF(N84+AC84=2,1,0)+IF(N85+AC85 =2,1,0)+IF(N86+AC86=2,1,0)+IF(N87+AC87=2,1,0) +IF(N88+AC88=2,1,0)+IF(N89+AC89=2,1,0)+IF(N90+AC90 =2,1,0)+IF(N91+AC91=2,1,0)+IF(N92+AC92=2,1,0) +IF(N93+AC93=2,1,0)+IF(N94+AC94=2,1,0)+IF(N95+AC95 =2,1,0)+IF(N96+AC96=2,1,0)+IF(N97+AC97=2,1,0) +IF(N98+AC98=2,1,0)+IF(N99+AC99=2,1,0)+IF(N100+AC1 00=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102+AC102=2,1,0 ) And that only covers Nik - Auckland, up to row 102. And is spread out over many cells, then summed as described earlier. Is there a way i can range this? Id want it to be something like =IF(N3:N300+AC3:AC300=2,1,0) so it does the same thing but covers all the rows, in one cell.. But that formula doesnt work... Any ideas? Im using Excel 2002. Yes, old school. Any help appriciated. Cheers. Nik. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a way
Strange way of arranging your data! You must have forgotten that zero
times anything is zero and 1 times 1 is 1, so if you multiply the cells together you will get either 1 or 0, thus dispensing with the need for IF. You then need to add the 1's and 0's together, and this is what SUMPRODUCT does. If your data stretches from rows 3 to 300, try this formula to add up the successes for Nik (column N) in Aukland (column AC): =SUMPRODUCT((N3:N300)*(AC3:AC300)) This will replace your monstrous formula quoted below. Hopefully you can see how to adapt it for other people and for other places. Hope this helps. Pete On Jul 25, 11:26 pm, N|X6S|X wrote: Ok. I have a formula that is too long to put in one cell. So it has been spread down 10 or so cell's then used =SUM(N3:N300) to add them together (right down the bottom of the spreadsheet hidden away). Im sure there is a way to get this smaller by using a relative range rather than a different formula for each row (and not have huge tables at the bottom doing all the working, just keep it to the one cell)... I need it to read 2 separate columns data only horizontally across the rows, but over a range of 300 rows. Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities.The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. So, came up with this.. =IF(N3+AC3=2,1,0) described as... (If column N (Nik) + column AC (Auckland) = 2, add 1 to this cell (the TOTAL which means Nik made a sale in Auckland), if it doesnt equal 2 then add nothing, no sale... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each... But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0 )+IF(N6+AC6=2,1,0)+IF(N7+*AC7=2,1,0) +IF(N8+AC8=2,1,0)+IF(N9+AC9=2,1,0)+IF(N10+AC10=2,1 ,0)+IF(N11+AC11=2,1,0)+IF*(N12+AC12=2,1,0) +IF(N13+AC13=2,1,0)+IF(N14+AC14=2,1,0)+IF(N15+AC15 =2,1,0)+IF(N16+AC16=2,1,0*)+IF(N17+AC17=2,1,0) +IF(N18+AC18=2,1,0)+IF(N19+AC19=2,1,0)+IF(N20+AC20 =2,1,0)+IF(N21+AC21=2,1,0*)+IF(N22+AC22=2,1,0) +IF(N23+AC23=2,1,0)+IF(N24+AC24=2,1,0)+IF(N25+AC25 =2,1,0)+IF(N26+AC26=2,1,0*)+IF(N27+AC27=2,1,0) +IF(N28+AC28=2,1,0)+IF(N29+AC29=2,1,0)+IF(N30+AC30 =2,1,0)+IF(N31+AC31=2,1,0*)+IF(N32+AC32=2,1,0) +IF(N33+AC33=2,1,0)+IF(N34+AC34=2,1,0)+IF(N35+AC35 =2,1,0)+IF(N36+AC36=2,1,0*)+IF(N37+AC37=2,1,0) +IF(N38+AC38=2,1,0)+IF(N39+AC39=2,1,0)+IF(N40+AC40 =2,1,0)+IF(N41+AC41=2,1,0*)+IF(N42+AC42=2,1,0) +IF(N43+AC43=2,1,0)+IF(N44+AC44=2,1,0)+IF(N45+AC45 =2,1,0)+IF(N46+AC46=2,1,0*)+IF(N47+AC47=2,1,0) +IF(N48+AC48=2,1,0)+IF(N49+AC49=2,1,0)+IF(N50+AC50 =2,1,0)+IF(N51+AC51=2,1,0*)+IF(N52+AC52=2,1,0) +IF(N53+AC53=2,1,0)+IF(N54+AC54=2,1,0)+IF(N55+AC55 =2,1,0)+IF(N56+AC56=2,1,0*)+IF(N57+AC57=2,1,0) +IF(N58+AC58=2,1,0)+IF(N59+AC59=2,1,0)+IF(N60+AC60 =2,1,0)+IF(N61+AC61=2,1,0*)+IF(N62+AC62=2,1,0) +IF(N63+AC63=2,1,0)+IF(N64+AC64=2,1,0)+IF(N65+AC65 =2,1,0)+IF(N66+AC66=2,1,0*)+IF(N67+AC67=2,1,0) +IF(N68+AC68=2,1,0)+IF(N69+AC69=2,1,0)+IF(N70+AC70 =2,1,0)+IF(N71+AC71=2,1,0*)+IF(N72+AC72=2,1,0) +IF(N73+AC73=2,1,0)+IF(N74+AC74=2,1,0)+IF(N75+AC75 =2,1,0)+IF(N76+AC76=2,1,0*)+IF(N77+AC77=2,1,0) +IF(N78+AC78=2,1,0)+IF(N79+AC79=2,1,0)+IF(N80+AC80 =2,1,0)+IF(N81+AC81=2,1,0*)+IF(N82+AC82=2,1,0) +IF(N83+AC83=2,1,0)+IF(N84+AC84=2,1,0)+IF(N85+AC85 =2,1,0)+IF(N86+AC86=2,1,0*)+IF(N87+AC87=2,1,0) +IF(N88+AC88=2,1,0)+IF(N89+AC89=2,1,0)+IF(N90+AC90 =2,1,0)+IF(N91+AC91=2,1,0*)+IF(N92+AC92=2,1,0) +IF(N93+AC93=2,1,0)+IF(N94+AC94=2,1,0)+IF(N95+AC95 =2,1,0)+IF(N96+AC96=2,1,0*)+IF(N97+AC97=2,1,0) +IF(N98+AC98=2,1,0)+IF(N99+AC99=2,1,0)+IF(N100+AC1 00=2,1,0)+IF(N101+AC101=2*,1,0)+IF(N102+AC102=2,1, 0) And that only covers Nik - Auckland, up to row 102. And is spread out over many cells, then summed as described earlier. Is there a way i can range this? Id want it to be something like =IF(N3:N300+AC3:AC300=2,1,0) so it does the same thing but covers all the rows, in one cell.. But that formula doesnt work... Any ideas? Im using Excel 2002. Yes, old school. Any help appriciated. Cheers. Nik. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a
Im going to give it a go, but im not trying to multiply, im basically using 1
as a marker, so at the bottom it works out how many appointments compared to sales. 1 in column N + 1 in column AC = 1 sale in the total cell box. But if there is a 1 in N and nothing in AC then nothing gets added. My formula is working perfectly. There must be a way to write exactlly the same thing to make it smaller tho, thats what im asking.. Il try all of these and get back to you's. Thanks. "Pete_UK" wrote: Strange way of arranging your data! You must have forgotten that zero times anything is zero and 1 times 1 is 1, so if you multiply the cells together you will get either 1 or 0, thus dispensing with the need for IF. You then need to add the 1's and 0's together, and this is what SUMPRODUCT does. If your data stretches from rows 3 to 300, try this formula to add up the successes for Nik (column N) in Aukland (column AC): =SUMPRODUCT((N3:N300)*(AC3:AC300)) This will replace your monstrous formula quoted below. Hopefully you can see how to adapt it for other people and for other places. Hope this helps. Pete On Jul 25, 11:26 pm, N|X6S|X wrote: Ok. I have a formula that is too long to put in one cell. So it has been spread down 10 or so cell's then used =SUM(N3:N300) to add them together (right down the bottom of the spreadsheet hidden away). Im sure there is a way to get this smaller by using a relative range rather than a different formula for each row (and not have huge tables at the bottom doing all the working, just keep it to the one cell)... I need it to read 2 separate columns data only horizontally across the rows, but over a range of 300 rows. Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities.The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. So, came up with this.. =IF(N3+AC3=2,1,0) described as... (If column N (Nik) + column AC (Auckland) = 2, add 1 to this cell (the TOTAL which means Nik made a sale in Auckland), if it doesnt equal 2 then add nothing, no sale... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each... But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0 )+IF(N6+AC6=2,1,0)+IF(N7+-AC7=2,1,0) +IF(N8+AC8=2,1,0)+IF(N9+AC9=2,1,0)+IF(N10+AC10=2,1 ,0)+IF(N11+AC11=2,1,0)+IF-(N12+AC12=2,1,0) +IF(N13+AC13=2,1,0)+IF(N14+AC14=2,1,0)+IF(N15+AC15 =2,1,0)+IF(N16+AC16=2,1,0-)+IF(N17+AC17=2,1,0) +IF(N18+AC18=2,1,0)+IF(N19+AC19=2,1,0)+IF(N20+AC20 =2,1,0)+IF(N21+AC21=2,1,0-)+IF(N22+AC22=2,1,0) +IF(N23+AC23=2,1,0)+IF(N24+AC24=2,1,0)+IF(N25+AC25 =2,1,0)+IF(N26+AC26=2,1,0-)+IF(N27+AC27=2,1,0) +IF(N28+AC28=2,1,0)+IF(N29+AC29=2,1,0)+IF(N30+AC30 =2,1,0)+IF(N31+AC31=2,1,0-)+IF(N32+AC32=2,1,0) +IF(N33+AC33=2,1,0)+IF(N34+AC34=2,1,0)+IF(N35+AC35 =2,1,0)+IF(N36+AC36=2,1,0-)+IF(N37+AC37=2,1,0) +IF(N38+AC38=2,1,0)+IF(N39+AC39=2,1,0)+IF(N40+AC40 =2,1,0)+IF(N41+AC41=2,1,0-)+IF(N42+AC42=2,1,0) +IF(N43+AC43=2,1,0)+IF(N44+AC44=2,1,0)+IF(N45+AC45 =2,1,0)+IF(N46+AC46=2,1,0-)+IF(N47+AC47=2,1,0) +IF(N48+AC48=2,1,0)+IF(N49+AC49=2,1,0)+IF(N50+AC50 =2,1,0)+IF(N51+AC51=2,1,0-)+IF(N52+AC52=2,1,0) +IF(N53+AC53=2,1,0)+IF(N54+AC54=2,1,0)+IF(N55+AC55 =2,1,0)+IF(N56+AC56=2,1,0-)+IF(N57+AC57=2,1,0) +IF(N58+AC58=2,1,0)+IF(N59+AC59=2,1,0)+IF(N60+AC60 =2,1,0)+IF(N61+AC61=2,1,0-)+IF(N62+AC62=2,1,0) +IF(N63+AC63=2,1,0)+IF(N64+AC64=2,1,0)+IF(N65+AC65 =2,1,0)+IF(N66+AC66=2,1,0-)+IF(N67+AC67=2,1,0) +IF(N68+AC68=2,1,0)+IF(N69+AC69=2,1,0)+IF(N70+AC70 =2,1,0)+IF(N71+AC71=2,1,0-)+IF(N72+AC72=2,1,0) +IF(N73+AC73=2,1,0)+IF(N74+AC74=2,1,0)+IF(N75+AC75 =2,1,0)+IF(N76+AC76=2,1,0-)+IF(N77+AC77=2,1,0) +IF(N78+AC78=2,1,0)+IF(N79+AC79=2,1,0)+IF(N80+AC80 =2,1,0)+IF(N81+AC81=2,1,0-)+IF(N82+AC82=2,1,0) +IF(N83+AC83=2,1,0)+IF(N84+AC84=2,1,0)+IF(N85+AC85 =2,1,0)+IF(N86+AC86=2,1,0-)+IF(N87+AC87=2,1,0) +IF(N88+AC88=2,1,0)+IF(N89+AC89=2,1,0)+IF(N90+AC90 =2,1,0)+IF(N91+AC91=2,1,0-)+IF(N92+AC92=2,1,0) +IF(N93+AC93=2,1,0)+IF(N94+AC94=2,1,0)+IF(N95+AC95 =2,1,0)+IF(N96+AC96=2,1,0-)+IF(N97+AC97=2,1,0) +IF(N98+AC98=2,1,0)+IF(N99+AC99=2,1,0)+IF(N100+AC1 00=2,1,0)+IF(N101+AC101=2-,1,0)+IF(N102+AC102=2,1,0) And that only covers Nik - Auckland, up to row 102. And is spread out over many cells, then summed as described earlier. Is there a way i can range this? Id want it to be something like =IF(N3:N300+AC3:AC300=2,1,0) so it does the same thing but covers all the rows, in one cell.. But that formula doesnt work... Any ideas? Im using Excel 2002. Yes, old school. Any help appriciated. Cheers. Nik. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a
Thanks, works perfect. I just changed the range you gave back to individual
cities as i need to know exactly where the sale was made in the total. And the sales people move form city to city. But works a charm. Mint! Nik "Harlan Grove" wrote: N|X6S|X wrote... .... Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities. The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. . . . .... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1, 0) .... IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102 +AC102=2,1,0) .... For all 4 cities at once, use =SUMPRODUCT(--(N3:N102+AC3:AF102=2)) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a
Oh, is there any chance you could give me a breif breakdown of what that
command consists of? This is the first time ive used spreadsheets really, so i thought i was doing well to have worked out my original formula.. lol. Cheers. "Harlan Grove" wrote: N|X6S|X wrote... .... Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities. The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. . . . .... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1, 0) .... IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102 +AC102=2,1,0) .... For all 4 cities at once, use =SUMPRODUCT(--(N3:N102+AC3:AF102=2)) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky LONG formula, need a shorter version, there has to be a
Follow this link for an excellent explanation about SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps. Pete On Jul 26, 12:48 am, N|X6S|X wrote: Oh, is there any chance you could give me a breif breakdown of what that command consists of? This is the first time ive used spreadsheets really, so i thought i was doing well to have worked out my original formula.. lol. Cheers. "Harlan Grove" wrote: N|X6S|X wrote... .... Basically i need to be able to know if a person has done a certain thing, in a certain place. Column N-X are the people, and column AC-AF are sales in certain cities. The columns N-X with 1's in them are appointments for the person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have made a sale from that appointment, if nothing, then no sale. . . . .... Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row 3, making a sale in Auckland). But i also need it to recognise Hamilton, and 2 other cities. Where i made another table, replacing AC with AD for each. But the amount of formula/equations is massively unecessary.. As it looks like this... =IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1, 0) .... IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102 +AC102=2,1,0) .... For all 4 cities at once, use =SUMPRODUCT(--(N3:N102+AC3:AF102=2))- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Formula too long - Shorter version? | Excel Worksheet Functions | |||
How do I break up a long text cell into shorter ones withoug s | Excel Discussion (Misc queries) | |||
Split Long Text Cell into Two Shorter Cells Without Splitting Word | Excel Discussion (Misc queries) | |||
Shorter Formula | Excel Discussion (Misc queries) |