Home

PLEASE HELP! Budget formula won't work

%3CLINGO-SUB%20id%3D%22lingo-sub-1011782%22%20slang%3D%22en-US%22%3EPLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1011782%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20budget%20spreadsheet%20that%20will%20have%203%20columns%3A%3C%2FP%3E%3CP%3Etask.%20%25%20allocated.%20%24%20amount.%3C%2FP%3E%3CP%3Eso%20for%20example%3A%20if%20the%20total%20budget%20is%20%24100%3A%3C%2FP%3E%3CP%3Etask%201.%2020%25.%20%2420%3C%2FP%3E%3CP%3Etask%202.%2050%25.%20%2450%3C%2FP%3E%3CP%3Eetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%2C%20I%20can't%20seem%20to%20input%20a%20formula%20on%20the%20%24%20amount%20column%2C%20in%20order%20to%20automatically%20calculate%20the%20%24%20based%20on%20the%20%25%20from%20total.%20I've%20tried%20%24%3D%25*total%2C%20or%20%24%3Dtotal%2F%25%2C%20but%20the%20result%20is%20so%20odd.%20it%20shows%20it%20momentarily%2C%20but%20won't%20update%20based%20on%20changes%20I%20make.%20it%20also%20won't%20copy%20the%20formula%20to%20bellow%20rows%2C%20it%20only%20copies%20the%20specific%20value%20(even%20though%20it%20does%20show%20the%20correct%20formula).%20I%20don't%20know%20if%20this%20matters%2C%20but%20this%20is%20all%20in%20a%20table%20(does%20that%20have%20an%20effect%20on%20formulas%3F).%20I've%20attached%20screenshots%20bellow.%20anyone%20please%20have%20any%20idea%20why%20this%20is%20happening%20or%20how%20to%20fix%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1011782%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1011887%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1011887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%20don't%20you%20use%20absolute%20references%2C%20e.g.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24D%242%2F%5B%40%5BAllocation%20%25%5D%5D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1011926%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1011926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20why%20it%20won't%20accept%20anything%3F%20I%20feel%20like%20it's%20such%20a%20simple%20formula..%20(screenshot%20attached)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1011954%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1011954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20Perhaps%20this%20will%20help.%20You%20seem%20to%20be%20using%20the%20%40%20symbol%2C%20which%20I've%20never%20used%20(which%20may%20mean%20I'm%20not%20at%20all%20qualified%20to%20even%20comment%2C%20I%20realize)%2C%20but%20here's%20what%20I%20learn%20from%20Google.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157456iDCFA1C481AD86547%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012131%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012131%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20thing%20is%20I'M%20not%20putting%20in%20the%26nbsp%3B%40%20sign.%20I'm%20simply%20choosing%20that%20row%20and%20that's%20what%20it%20generates.%20I%20thought%20it%20might%20be%20because%20it's%20in%20a%20table%20(I%20chose%20the%20data%20and%20clicked%20%22format%20as%20table%22)%20but%20I'm%20not%20sure%20that%20has%20anything%20to%20do%20with%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012261%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012261%22%20slang%3D%22en-US%22%3E%3CP%3Eis%20it%20possible%20to%20upload%20your%20actual%20spreadsheet%20so%20Sergei%20and%20I%20could%20look%20at%20it%20rather%20than%20screen%20shots%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20I'm%20on%20Excel%20for%20Mac%2C%20so%20don't%20have%20all%20the%20functionality%20that%20you%20might%2C%20depending%20on%20your%20system.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012358%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012358%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20tried%20it%20again%20and%20rather%20than%20clicking%20on%20the%20row%20I%20manually%20put%20the%20cell%20number%20and%20that%20worked%20(%3D%24D%242*C4)%20so%20I%20guess%20it's%20solved.%20I%20would%20love%20to%20understand%20why%20I%20couldn't%20just%20pick%20the%20specific%20cell%2C%20but%20maybe%20that%20has%20to%20do%20with%20the%20table%20formatting%3F..%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20I'm%20on%20Mac%20as%20well%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012362%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20440px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157459i1CBDDAC8AA5879C8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20guess%20it%20shall%20be%20multiplication%20instead%20of%20dividing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012365%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20entirely%20possible%20that%20it's%20a%20Table%20thing.%20What%20you%20did%20is%20what%20I%20was%20thinking...and%20that%20should%20copy%20down%20the%20rest%20of%20the%20column%20too!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012366%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20Tables%20better%20to%20work%20with%20structured%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012367%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012367%22%20slang%3D%22en-US%22%3E%3CP%3ENope%2C%20won't%20work%20for%20me%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012369%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20enter%20the%20formula%3F%20If%20manually%20type%20%3D%24D%242*%20and%20when%20click%20on%20the%20cell%20in%20column%20Allocation%20%25%20in%20the%20same%20row%20-%20it%20shall%20work.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012370%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012370%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20exactly%20what%20I%20did%20and%20that%20error%20message%20shows%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012372%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012372%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20correction.%20if%20I%20simply%20choose%20the%20cell%2C%20the%20formula%20would%20be%3A%26nbsp%3B%3D%24D%242*%5B%40%5BAllocation%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20doesn't%20have%20the%20%25%5D%5D%20at%20the%20end.%20if%20I%20add%20it%2C%20the%20error%20message%20shows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1012374%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP!%20Budget%20formula%20won't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1012374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F448653%22%20target%3D%22_blank%22%3E%40stpstein%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20nothing%20manually%2C%20use%20column%20name%20as%20it%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
stpstein
Occasional Contributor

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!

 

14 Replies

@stpstein 

Why don't you use absolute references, e.g.

=$D$2/[@[Allocation %]]

 

@Sergei Baklan 

 

I don't know why it won't accept anything? I feel like it's such a simple formula.. (screenshot attached)

@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.

clipboard_image_0.png

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. 

@mathetes 

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.

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

@stpstein 

That looks like

image.png

I guess it shall be multiplication instead of dividing.

@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!

@stpstein 

If you use Tables better to work with structured references.

@stpstein 

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. 

That's exactly what I did and that error message shows @Sergei Baklan 

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

 

@Sergei Baklan 

@stpstein 

Add nothing manually, use column name as it is.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies