Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
Hi
I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
There are no 'yellow' squares. More explanation is needed.
-- John C "steve" wrote: Hi I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
Sorry John
The colour was there when I sent the email. I can send the excel sheet if it helps. Input A 1 2 3 4 5 6 7 8 9 10 11 12 Total - 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 Total - 20 Input B 3 3 4 5 6 7 8 9 10 11 12 Total - 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 -------- 68 -------- If a line was drawn from the top row on the number 8 to the number 3 in the fiest column. It would touch a number of cells. If the numbers in these cells were added together ... eg line 1 - 1+2+3+4+5+6+7+8+9+10+11+12 = 36 line 2 - 2+3+4+5+6 = 20 line 3 - 3+4+5 = 12 Total Score of 36+20+12 = 68. I need a way of just taking in the above inputs eg 8 and 3 and returning 68. Cheers Steve "John C" <johnc@stateofdenial wrote in message ... There are no 'yellow' squares. More explanation is needed. -- John C "steve" wrote: Hi I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
Hi. It's not too clear, but when A & B are equal, you can sum the diagonal, and all to the left via:
n=12 ?(1/6)*n*(1 + n)*(1 + 2*n) 650 I'm not clear on your rule where "3 in column 1" equals 3+4+5 = 12 - - HTH :) Dana DeLouis "steve" wrote in message ... Sorry John The colour was there when I sent the email. I can send the excel sheet if it helps. Input A 1 2 3 4 5 6 7 8 9 10 11 12 Total - 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 Total - 20 Input B 3 3 4 5 6 7 8 9 10 11 12 Total - 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 -------- 68 -------- If a line was drawn from the top row on the number 8 to the number 3 in the fiest column. It would touch a number of cells. If the numbers in these cells were added together ... eg line 1 - 1+2+3+4+5+6+7+8+9+10+11+12 = 36 line 2 - 2+3+4+5+6 = 20 line 3 - 3+4+5 = 12 Total Score of 36+20+12 = 68. I need a way of just taking in the above inputs eg 8 and 3 and returning 68. Cheers Steve "John C" <johnc@stateofdenial wrote in message ... There are no 'yellow' squares. More explanation is needed. -- John C "steve" wrote: Hi I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
It would also depend on how wide the columns are. You also state starting on
8 for Input A, but then you say =1+2+3+4+5+6+7+8+9+10+11+12, but then you say that =36, but that is only =1+2+3+4+5+6+7+8 -- John C "steve" wrote: Sorry John The colour was there when I sent the email. I can send the excel sheet if it helps. Input A 1 2 3 4 5 6 7 8 9 10 11 12 Total - 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 Total - 20 Input B 3 3 4 5 6 7 8 9 10 11 12 Total - 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 -------- 68 -------- If a line was drawn from the top row on the number 8 to the number 3 in the fiest column. It would touch a number of cells. If the numbers in these cells were added together ... eg line 1 - 1+2+3+4+5+6+7+8+9+10+11+12 = 36 line 2 - 2+3+4+5+6 = 20 line 3 - 3+4+5 = 12 Total Score of 36+20+12 = 68. I need a way of just taking in the above inputs eg 8 and 3 and returning 68. Cheers Steve "John C" <johnc@stateofdenial wrote in message ... There are no 'yellow' squares. More explanation is needed. -- John C "steve" wrote: Hi I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex formula
This might be what you are looking for, test it and see.
Assuming your data of numbers starts in A1, down to row 12, and across to column L. I used the following formula in column M. M1: =IF(ROW()InputB,"",SUM(OFFSET($A1,0,0,1,IF(ROUND( InputA-((ROW()-1)*InputA/InputB),0)=0,1,ROUND(InputA-((ROW()-1)*InputA/InputB),0))))) Copy this formula down to M12 Total: =SUM(M1:M12) Hope this helps. -- John C "steve" wrote: Hi I need some help,please. I need a formula to claculate a value when input A and B are inserted. The table below details the values I am working with and depending on the values the results will change. If we drew a line from 8 and 3 the yellow squares would be used in the calculation. Input A 1 2 3 4 5 6 7 8 9 10 11 12 36 Input A 8 2 3 4 5 6 7 8 9 10 11 12 20 Input B 3 3 4 5 6 7 8 9 10 11 12 12 Result 68 4 5 6 7 8 9 10 11 12 Input B 5 6 7 8 9 10 11 12 6 7 8 9 10 11 12 7 8 9 10 11 12 8 9 10 11 12 9 10 11 12 10 11 12 11 12 12 68 If we changed the inputs and the position of the line the following squares would be used. Input A 1 2 3 4 5 6 7 8 9 10 11 12 78 Input A 12 2 3 4 5 6 7 8 9 10 11 12 77 Input B 12 3 4 5 6 7 8 9 10 11 12 75 Result 650 4 5 6 7 8 9 10 11 12 72 Input B 5 6 7 8 9 10 11 12 68 6 7 8 9 10 11 12 63 7 8 9 10 11 12 57 8 9 10 11 12 50 9 10 11 12 42 10 11 12 33 11 12 23 12 12 650 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Formula | Excel Discussion (Misc queries) | |||
Complex If/Then formula? | Excel Discussion (Misc queries) | |||
Can someone help with this complex formula? | Excel Worksheet Functions | |||
Need Help with complex formula | Excel Worksheet Functions | |||
complex formula | Excel Worksheet Functions |