|
Problem with an "if" relating to a range
May not have explained my problem clearly enough so here it is again,
hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Hi!
Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Getting a #value error coming up
Any ideas anthony "Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc
"Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Hi!
What is the *exact* formula that you used? could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc That should not make any difference. If they were all dates or if they were all just text strings that would not make a difference. If D7 was a text string and E29:E41 were true dates or vice versa, the formula would return an incorrect result but not a #VALUE! error. Are the values in C29:C41 and B5 really numbers? The only things that will cause a #VALUE! error are if the values in C29:C41 or B5 are really TEXT or if the range sizes are not the same. Biff "Anthony" wrote in message ... could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc "Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Sorry bout delay in reply, i went to bed for a while.
Exact formula is =SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) A B C D E 28 Purchases # $/Hd Mth 29 Feeder Steers 100 412.5 Jul-05 30 Feeder Steers 34 412.5 Aug-06 31 32 33 34 checked to make sure formats are correct. anthony "Biff" wrote: Hi! What is the *exact* formula that you used? could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc That should not make any difference. If they were all dates or if they were all just text strings that would not make a difference. If D7 was a text string and E29:E41 were true dates or vice versa, the formula would return an incorrect result but not a #VALUE! error. Are the values in C29:C41 and B5 really numbers? The only things that will cause a #VALUE! error are if the values in C29:C41 or B5 are really TEXT or if the range sizes are not the same. Biff "Anthony" wrote in message ... could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc "Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Ok, you have mismatched range sizes. The ranges must be the EXACT same size:
=SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) 'Cattle Mvts'!E29:'Cattle Mvts'!E42 'Cattle Mvts'!C29:'Cattle Mvts'!C41 Those are not the same size! You either have to change E42 to E41 or, C41 to C42 Also, it's not necessary to repeat the sheet name: 'Cattle Mvts'!E29:'Cattle Mvts'!E42 'Cattle Mvts'!E29:E42 Note! Exact range sizes means the exact same length or width: A1:A5 and B2:B6 are the exact same size! Also, you don't need the extra ( ): ........),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) Should be: ......),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... Sorry bout delay in reply, i went to bed for a while. Exact formula is =SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) A B C D E 28 Purchases # $/Hd Mth 29 Feeder Steers 100 412.5 Jul-05 30 Feeder Steers 34 412.5 Aug-06 31 32 33 34 checked to make sure formats are correct. anthony "Biff" wrote: Hi! What is the *exact* formula that you used? could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc That should not make any difference. If they were all dates or if they were all just text strings that would not make a difference. If D7 was a text string and E29:E41 were true dates or vice versa, the formula would return an incorrect result but not a #VALUE! error. Are the values in C29:C41 and B5 really numbers? The only things that will cause a #VALUE! error are if the values in C29:C41 or B5 are really TEXT or if the range sizes are not the same. Biff "Anthony" wrote in message ... could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc "Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
Thanks Biff
I finally saw the mistake myself Thanks for the patience "Biff" wrote: Ok, you have mismatched range sizes. The ranges must be the EXACT same size: =SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) 'Cattle Mvts'!E29:'Cattle Mvts'!E42 'Cattle Mvts'!C29:'Cattle Mvts'!C41 Those are not the same size! You either have to change E42 to E41 or, C41 to C42 Also, it's not necessary to repeat the sheet name: 'Cattle Mvts'!E29:'Cattle Mvts'!E42 'Cattle Mvts'!E29:E42 Note! Exact range sizes means the exact same length or width: A1:A5 and B2:B6 are the exact same size! Also, you don't need the extra ( ): ........),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) Should be: ......),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... Sorry bout delay in reply, i went to bed for a while. Exact formula is =SUMPRODUCT(--('Cattle Mvts'!E29:'Cattle Mvts'!E42='Cattle Budget'!E7),('Cattle Mvts'!C29:'Cattle Mvts'!C41)*'Feedlot Assumptions'!B5) A B C D E 28 Purchases # $/Hd Mth 29 Feeder Steers 100 412.5 Jul-05 30 Feeder Steers 34 412.5 Aug-06 31 32 33 34 checked to make sure formats are correct. anthony "Biff" wrote: Hi! What is the *exact* formula that you used? could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc That should not make any difference. If they were all dates or if they were all just text strings that would not make a difference. If D7 was a text string and E29:E41 were true dates or vice versa, the formula would return an incorrect result but not a #VALUE! error. Are the values in C29:C41 and B5 really numbers? The only things that will cause a #VALUE! error are if the values in C29:C41 or B5 are really TEXT or if the range sizes are not the same. Biff "Anthony" wrote in message ... could it be that E29 to E41 and d7 are all dates showing as Jul 05 etc "Biff" wrote: Hi! Try this: =SUMPRODUCT(--('Cattle Mvts'!E29:E41='Cattle Budget'!D7),'Cattle Mvts'!C29:C41)*'Feedlot Assumptions'!B5 Biff "Anthony" wrote in message ... May not have explained my problem clearly enough so here it is again, hopefully someone is able to help.The formula below works well for a set scenario =IF('Cattle Budget'!D7='Cattle Mvts'!E29,'Cattle Mvts'!C29*'Feedlot Assumptions'!B5,0) however what I now need is that if the date in D7 is equal to the date showing in any of E29 to E41 then multiply the corresponding row in the C column by B5, and add them all togther. Hopefully someone can help thanks anthony |
| All times are GMT +1. The time now is 12:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com