Nov 15 2019 10:34 AM
Hi,
I'm trying to create a budget spreadsheet that will have 3 columns:
task. % allocated. $ amount.
so for example: if the total budget is $100:
task 1. 20%. $20
task 2. 50%. $50
etc.
For some reason, I can't seem to input a formula on the $ amount column, in order to automatically calculate the $ based on the % from total. I've tried $=%*total, or $=total/%, but the result is so odd. it shows it momentarily, but won't update based on changes I make. it also won't copy the formula to bellow rows, it only copies the specific value (even though it does show the correct formula). I don't know if this matters, but this is all in a table (does that have an effect on formulas?). I've attached screenshots bellow. anyone please have any idea why this is happening or how to fix it?
Thank you!
Nov 15 2019 11:22 AM
Nov 15 2019 11:26 AM - edited Nov 15 2019 11:27 AM
I don't know why it won't accept anything? I feel like it's such a simple formula.. (screenshot attached)
Nov 15 2019 11:29 AM
@stpstein Perhaps this will help. You seem to be using the @ symbol, which I've never used (which may mean I'm not at all qualified to even comment, I realize), but here's what I learn from Google.
Nov 15 2019 11:31 AM
Hi,
the thing is I'M not putting in the @ sign. I'm simply choosing that row and that's what it generates. I thought it might be because it's in a table (I chose the data and clicked "format as table") but I'm not sure that has anything to do with it.
Nov 15 2019 11:34 AM - edited Nov 15 2019 11:35 AM
is it possible to upload your actual spreadsheet so Sergei and I could look at it rather than screen shots?
By the way, I'm on Excel for Mac, so don't have all the functionality that you might, depending on your system.
Nov 15 2019 11:37 AM - edited Nov 15 2019 11:37 AM
I just tried it again and rather than clicking on the row I manually put the cell number and that worked (=$D$2*C4) so I guess it's solved. I would love to understand why I couldn't just pick the specific cell, but maybe that has to do with the table formatting?.. @mathetes
and I'm on Mac as well :)
Nov 15 2019 11:39 AM
Nov 15 2019 11:40 AM
@stpstein entirely possible that it's a Table thing. What you did is what I was thinking...and that should copy down the rest of the column too!
Nov 15 2019 11:40 AM
If you use Tables better to work with structured references.
Nov 15 2019 11:41 AM
Nope, won't work for me @Sergei Baklan
Nov 15 2019 11:44 AM
How do you enter the formula? If manually type =$D$2* and when click on the cell in column Allocation % in the same row - it shall work.
Nov 15 2019 11:44 AM
That's exactly what I did and that error message shows @Sergei Baklan
Nov 15 2019 11:46 AM - edited Nov 15 2019 11:47 AM
Sorry, correction. if I simply choose the cell, the formula would be: =$D$2*[@[Allocation
it doesn't have the %]] at the end. if I add it, the error message shows
Nov 15 2019 11:48 AM
Add nothing manually, use column name as it is.