Quick excel question...this is driving me nuts, because it seems like it should be simple. I'm trying to insert a header into my spreadsheet. I want the header to read W&P I, Inc. when I click out of the header, it shows W1 I, Inc. and then when you click inside the header again it shows W&[Page] I, Inc. So obviously it's changing the "P" to the page number, how do I turn that off? I've looked throughout the autocorrect feature but haven't been able to find out where I can stop it from auto changing it. thanks!
nevermind! found out you just have to use double ampersands to get around the autocorrect. W&&P I, Inc. disregard plz!
Really need some help on this..... I am trying to divide a number in one cell, among a range of cells, but each cell is weighted differently. For example 16 divided by 4 cells, where the first 2 cells are worth 30% of 16, and the other 2 cells are worth 20% of 16. When added together I need the whole amounts, not decimals, not to exceed 16. ...Thanks in advance for any help its greatly needed
Not sure if I completely understand what you're trying to do. You want to multiply a number (16) by percentages? If 16 is in cell A1, and assuming your percentages are in A2, A3, A4 and A5. In B2 you could enter "=$A$1*A2", and then drag that down to B5. The amounts returned should total 16. Is that what you're trying to do?
No not exactly.... I have an inventory number(could be 16/14/8), and I want to divide that number among 4 cells(B1/C1/D1/E1), but not evenly. So each cell is weighted a different amount from the total, B1 is weighted as 30%, C1 is 30%, D1 is 25%, and E1 is 15%. Now I could easily go into each cell and multiply their weights by the inventory number(30%*16), but I also want whole numbers. So the problem is, when I add all the cells together, sometimes they either add up to more/less than the inventory number.
Example..... A1 B1(30%) C1(30%) D1(25%) E1(5%) (16) 4.8-->(5) 4.8-->(5) (4) 2.4-->(2) ....So the numbers are rounded to whole numbers but when added together they do not exceed 16
Can you make one cell (like E2) always be the loser when you exceed the total? So, you due a B2=ROUND(A1*B1,0), same for C2 thru D2. Then in E2 you say =A1-SUM(B22). That way even it would sometimes round down a number you'd ordinarily round up, but the total would be preserved.
Actually that works great!! Thank you for your help with that, really really appreciate it. Also, is there anyway I can have it choose randomly from which one to take from as well, instead of me choosing?
What a weird typo.... Randomness is possible but not advisable. you can put a RAND() command in and then an if/then statement in each cell linking to that RAND() command to dictate which answer to give, the rounding or the squeeze. The first problem is the needless complexity. The second problem is that it will change which bin gets the squeeze each time you refresh calcs -- which may or may not be a bad thing, I suppose. Depends on how important randomness is. If you can live without it, I would.
