Massive excel calculation help

[lostboyca]

I would like some help on excel calculation using two worksheets and numerous of variables.

The setup….

Worksheet A (Each row has a category title with the following heading)
Device Type {Type A, Type B, Type C, Type D}
Support Status {Support A, Support B, Support C, Support D}
Test Strategy {Test A, Test B, Test C}
Windows 2K {Text}
Windows XP {Text}

Worksheet B This is the calculation worksheet (Each row has a category title with the following heading)

First Calculation
Calculate from Worksheet A if row eqeual Device Type {Type A}, Support Status {Support B}, Test Strategy {Test A} then divide by Windows 2K {Row with any text} equal Average %

Second Calculation
from Worksheet A if all cells with Device Type {Type A}, Windows 2K (with text only) then divide Windows 2K (row with or without text) equal Average %

I can send the excel document for better detail if you are willing to help. Thanks in advance.

 

[tactps]

Welcome.

Post your workbook using the HTML Maker and advise some examples of what you want the results to be.

 

[lostboyca]

Here's the link http://www.diversityrus.com/Excel_help.htm

 

[tactps]

I am unable to read your link.

Refer to:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
on how to post to the board.

 

[lostboyca]

I did not understand the instructions so I've attached another link with a zip file and a htm file which I tired on a seperate machine and it worked. if this doesn't work I would like to e-mail you the excel. http://www.diversityrus.com/excel_help.htm or http://www.diversityrus.com/excel_help.zip

 

[BryanKollar]

Just what I was doing!

I just learned Excel by self-teaching, and this is one of the things I just did on my own. I have so many different if-and calculations, and if those calculations are different, then something else happens. I understand what you are requesting. You can download my spreadsheet at www.BryansRebates.com/NSI.XLS

Using this as an example should help you out. I don't have the exact code to copy & paste for you, but downloading this and checking it out may help. Don't be alarmed when you see the "MACRO" warning. That is only to "clear" my entire spreadsheet so I can start to re-enter data. Just cancel the enabling macros.

Let me know if this helps. I can't believe how much I learned on my own with this awesome tool.

 

[tactps]

The spreadsheet looks good, and I admire the fact that you've done it from scratch.

Unfortunately, I still have no idea what you want to achieve.

Please post a very specific example of what you have in each sheet, what you want it to calculate and what you want the result to be.

Perhaps start with one issue. Once resolved we'll move onto the next.

 

[lostboyca]

8.1 Report Tab
Under the Pass criteria
I would like the calculation to find anything in A8 – A176 that has the word “Capture Card/Device”; B8-B176 to have the word “Pass"; C8-C176” to have the word “Media Import”; Any word or number in L8-L176 then divide by the text that has the word “Capture Card/Device” in A8-A176. Which hopefully will give me the average of pass criterias


I have also updated the excel to make sure all the information is understandable

Hope that helps


Thanks,

 

[PA HS Teacher]

Not 100% sure what you want to do,

It sounds like you have a calculation where you want to include all rows that meet 3 conditions.

Though I'm not sure what that calculation is or how you can "divide by the text that has..." How can you divide by text?

It sounds like you may want to have a look at the sumproduct function.

 

[lostboyca]

not divide by text to calculate the total number of cells with that text