Ask Experts Questions for FREE Help !
Ask
    donf's Avatar
    donf Posts: 5,679, Reputation: 582
    Printers & Electronics Expert
     
    #1

    Sep 4, 2017, 07:19 AM
    Set up XL formula to calculate a column and ret percent
    I have a column of expenses C8:C21. The column is defined as Currency and is all negative values.

    I want to create a formula that will fill in another column with the percent for each dollar amount in the expense column.

    I have a name defined "inc" it contains "3435/*-1" The minus one is needed to convert the percentage to a positive value.
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #2

    Sep 5, 2017, 12:30 PM
    You want to show percent of what? Percent of the total expenses? Give us a simple example of what you're trying to do so we can help. But if that's all you need then the percentage of each expense is the value of that expense divided by the sum of all expenses.
    donf's Avatar
    donf Posts: 5,679, Reputation: 582
    Printers & Electronics Expert
     
    #3

    Sep 5, 2017, 01:56 PM
    Yes, I know that. The formula I use is "=C14/inc" inc is a defined name and contains 3435*-1. This will yield a positive percentage. The value of C14 will always be a negative number.

    I have a column of "currency" values, C14:C25 that I want the formula to process automatically without having to enter the formula on each cell of an adjacent column.

    N14 - - =C14/inc >> -1500/3435*-1 = 43%
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #4

    Sep 6, 2017, 05:10 AM
    Why not create a name called "pct," which equals c14/(c$14:c$25). Then the formula to use in cell N14 is simply "=pct". You can copy and paste that to all other cells C15 through C25.
    donf's Avatar
    donf Posts: 5,679, Reputation: 582
    Printers & Electronics Expert
     
    #5

    Sep 6, 2017, 06:47 AM
    I'll try that now. THANKS!
    ebaines's Avatar
    ebaines Posts: 12,131, Reputation: 1307
    Expert
     
    #6

    Sep 6, 2017, 07:24 AM
    Sorry, what I should have written is pct equals c14/sum(c$14:c$25).
    donf's Avatar
    donf Posts: 5,679, Reputation: 582
    Printers & Electronics Expert
     
    #7

    Sep 6, 2017, 07:55 AM
    What you gave me does work and yield the value I need for the column. The modified formula I used is "=$C6:$C20/(3435*-1).

    That gets the correct values for each column entry. I realize I could have used "/-3435" but I want to show how the sign is changed.

    Now the problem is that even though I declared the name as "workbook" when I go to use it on a different worksheet it posts an incorrect value.

    I edited the formula and found that it is because value that it pulls from C6 is from another worksheet. In the Name Manager dropdown, edit =pct, I found that the formula is linked to a specific worksheet, for example" ='Sept Fixed Expenses'!$C7:$C23/(3435*-1)"

    If I use the same formula for the August worksheet I get the September values plugged into the August column.

    Do I have to rename and then define the formula for each page I need to use it on?

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Calculate the pressure exerted by 76 column of mercy ? [ 3 Answers ]

Can anyone help me with this question.. It would be a great favor :) Thankzz in advance.. :)

Formula for percent [ 0 Answers ]

The formula I use is not giving me the right answer.Can someone please help. I used cost of good sold percent=cost of goods/net sales. My book does not give any other formulas for figuring the percentages. Here is the problem. The blank spots are the percents I need. At the end of 2012 the...

How to calculate row and column reference in access? [ 1 Answers ]

Dear Sir, I have am facing problem in access programme development if you can do so please solve it at the earliest. I have to created 5 tables for the determination of sales individually and then merged all the tables to each other to find out the actual transaction in the whole day. Fields of...

How to calculate the percent of three purchase assets [ 0 Answers ]

How do u calculate the percent of three purchase assets

Calculate the percent composition of copper [ 1 Answers ]

Calculate the percent composition of copper in the hydrate CuSO4----5H2O based on the atomic weight data.


View more questions Search