How to Assign Monetary Value to Percentages?

Well there was a slight change, so now it looks like I can't use the values as stated in the Key. I will be taking a specific value from a list in Sheet 2 (Example - Sheet 2, Column U, Rows 1-55 each have their own specific value that correspond to Sheet 1, Row 1, Columns B-BD).

I think I'm just confusing myself at this point.

I'm thinking that there's some way to make a Sheet 3 that mirrors Sheet 1 (as seen in my image attached) but have them show up as monetary value instead of percentages, and then have a fairly simple formula that combines Sheet 1 and Sheet 2 into Sheet 3 to get the Total Value?

Originally Posted by NBVC

How do you know when a column is Virtual versus Physical?

Sorry, that's what the change was. Disregard the Key and Virtual vs. Physical because now each server will have its own assigned value in Sheet 2.

Thanks for your quick reply.

Basically, the values for each server in Sheet 2 would correspond to the Total Percent which should always total 100%.

So, if Server 1 has a value of $100, and it has one process that takes up 50% and another process that takes up 50%, each would equal $50. Now, I want to see how much Process 1 costs to run between all servers. So, if we determined that cell B4 is worth $50, we would have to calculate that for all of the remaining cells. Then I would need to total up each row (process) for a total $ amount in column I.

I really hope that makes sense!

Oh and another thing I didn't mention is that the info in Sheet 2 is important in the fact that the value for each server on Sheet 1 will always be coming from Column U in Sheet 2. The data in that column will be replaced every month with new numbers (copied and pasted from another spreadsheet monthly), so whatever formula I use will have to incorporate that... is that possible? Sorry for all the questions.

Originally Posted by NBVC

Try:

=SUMPRODUCT(SUMIF(Sheet2!$A$2:$A$8,Sheet1!$B$1:$H$1,Sheet2!$B$2:$B$8)*$B2:$H2)

copied down

Thanks! I copy this down Column I?

I copied this into Cell I4 and it gave me 540. I did the manual calculations and came up with $610 for Process 3.

I copied it in the rest of Column I and it gave me errors.

I'm assuming each Row in Column I will have to have it's own formula to correspond with Sheet 2's values.

Edit: I didn't see your most recent post. Thanks a bunch for your help, I'll play around with it and let you know how it works!

Originally Posted by NBVC

See attached

Okay, I think I'm figuring it out slowly but surely Now I just have to apply this to my massive "real" spreadsheet!

You've been a big help, thanks so much.

Oh trust me, I will! I'm still waiting for my boss to get off his call which will determine the exact percentages. Once I get everything imported I will check my formulas.

I have already calculated my formulas (based on yours) to look like this

Process 1:
=SUMPRODUCT(SUMIF(Sheet2!$A$1:$A$55,Sheet1!$B$1:$BD$1,Sheet2!$U$1:$U$55)*$B2:$BD2)

Process 2:
=SUMPRODUCT(SUMIF(Sheet2!$A$1:$A$55,Sheet1!$B$1:$BD$1,Sheet2!$U$1:$U$55)*$B3:$BD3)

etc., and I think it will work.

Well, I tried what you said but it doesn't seem to do anything when I click and drag. I already made the formulas, fortunately there were only 13 rows of Processes.

Well entered the first formula into the cell. I clicked it like you said and dragged down to the bottom of the column, but it just highlights the cells, it doesn't copy anything. I checked and calculations are set to automatic.

I'm not too worried about that for this project, although it would be nice to know how to do it in the future.

Update: We changed the names of the worksheets. So it is not Sheet1, Sheet2 anymore. I edited the formula to reflect the names of the worksheets and it works!

My boss is super happy with me. Thanks for all your help!

Probably, would you like a cookie? lol