)Īnd finally this sum of "wanted" Ammount values is substracted from Initial value. If you use TRUE/FALSE values in numeric calculation they are treated as 0/1 values. Result is array (same size as Payments Who column) with TRUE/FALSE values.
How it works: value from Left to pay table column Who in same row where formula is with whole column Who in Payments table. Formula above must be confirmed by CTRL+SHIFT+ENTER Then formula in Left to pay column is Office versionsĪrray formula has to be used.
I suggest to format your tables ( named tables and renamed tables are used) like this: Try to not use horizontal tables (Left to pay) it is not how Excel (and others) are meant to be used.
Cell J6 is =SUM(J2:J3) and drag to right untill O6.Īnyways, probably there are a lot of ways doing this.You need to have "initial" Left to pay value stored in some cell and then calculate value like this: LeftToPay = InitialLeftToPay - SUM(AllPaymentsOfPerson) Rest of columns are formulas.Ĭell K2 got this formula: =VLOOKUP($I2,$A$2:$E$4,MATCH(K$1,$A$1:$E$1,0) FALSE)*$J2ĭrag to right this formula untill cell N2 and then drag down (in my example is just 2 rows).įormula in cell O2 is =SUM(J2:N2) just drag down too.įinally, the grand totals row is just another summatory of everything above. Formulas are placed into cells whenever you want Excel to add, subtract, multiply, divide or do other mathematical calculations. The right side needs manual input only in columns I (Pallet type) and J (quantity of pallets). The left side is source data so everything is manual input. You just input what type of pallet you want and how many and everything will be calculated. This example allows you to manage different types of pallets (let's say pallet A can hold only 1 apple, but pallet C can hold 9 apples) and calculate everything on the right side. (AutoSum can also work horizontally if you select an empty cell to the right of the cells to be summed. Excel will automatically sense the range to be summed. Then on the Formula tab, click AutoSum > Sum. Just select an empty cell directly below a column of data. As I said in my comment, the question is unclear but this is just and example of how you could store your data and then, depending on how many pallets you choose, calculate subtotals and grandtotals for each fruit: One quick and easy way to add values in Excel is to use AutoSum.
0 kommentar(er)
