# Solved: Need help with complex excel functions

Discussion in 'Business Applications' started by emydee23, Jan 12, 2009.

Thread Status:
Not open for further replies.
Advertisement
1. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
Please help!!!

In the attached file, I have (with help) figured out the majority of the formulas needed. There are 3, however, that I can not get.

Here is what I can not figure out:

Issue # 1: Disregard the notes in "account 2 on" tab. What I need to do is the following:
If the percentages of G8:AY8 are selected AND of G9:AY9 are selected, I need it to show in D12
90% 100% "Platinum"
80% 90% "Gold"
60% 70% "Silver"
Less than 60% And less than 70% "Not to standard"

Issue#2: In D49, I need Platinum to = 1, Gold = 2, Silver = 3 and Not to Standard = 4. I need whatever the lowest of the 4 is, or how the boss put it, the lowest common denominator.

Issue #3: On the master tab, there are spots for percentages. I need to know what percentage are Platinum, Gold, Silver and Not to Standard.

Any and all help will be greatly appreciated!!!

Here is a link to the file.

http://www.savefile.com/projects/808722413

2. ### Albantar

Joined:
Dec 9, 2008
Messages:
95
I'm sorry but I was unable to download the file from the location where you put it... Could you please attach it to the forum by clicking on the "Manage Attachments" button when you post a message? Thanks!

3. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
It was too large to attach as an excel file, I had to zip it. Let me know if you have any issues. If it doesn't work, I can send as an attachment in a private message.

File size:
44.1 KB
Views:
30
4. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
I attached the file. Thanks for looking into this for me!

5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,962
First Name:
Wayne
not sure I fully understand the requirement

are you trying to work out in G8-AY9 how many Xs you have and what percentage of the totla possible selections of Xs there are
so you have45 possible X in row 8 and 45 in row 9 = total = 90

so how many Xs are there as a percent of the possible 90 is that correct or is it only the cells that are not black that are the total number

You then have 45 cells on account-1 On TAB
so that = 50%

in D12 that would read "not to standard"
in D49 that would read 4

anywhere near correct ?

6. ### Albantar

Joined:
Dec 9, 2008
Messages:
95
I got the file but I'm afraid that I don't understand... You say "when the percentages of G8:AY8 are selected" but I see no percentages there, only blanks and x-es... Is this the correct file?

7. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I am also unsure what is happening here, starting with the locations. For instance, you state
"If the percentages of G8:AY8 are selected AND of G9:AY9 are selected, I need it to show in D12...."
G8:AY9 on which sheet? I am pretty sure you mean D12 on the Master worksheet.
And as for G8:AY9, assuming any one sheet, what does "are selected" mean? Has an X? No X? And if the 2 account sheets differ, which takes precedence?
Also, as Albantar stated, assuming the X means you have it in stock, do you mean something like (Count of X)/(Total possible X)?
If that is what you want, then I would suggest you redesign your worksheet somewhat to make numerical work easier, although a simple
=COUNTIF(G8:AY9,"X")
will count the # of X in those cells.

8. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
After reading your posts, I realize I didn't explain it quite right! I sincerely apologize!!!

I also needed to reattach the file. I attached one missing data.

In order to give you a better understanding of what I am trying to do, I will explain the project as it was explained to me as opposed to simply using the cell ranges. Unfortunately, the way they set it up is howthey want it. I begged them to restructure, but they didn't want to hear of it. Just my luck!

This is a tracking sheet that is going to be used by sales reps. Each sheet within the workbook will contain data for each account to allow the reps to understand where they need to improve.

Going backwards and starting with question 3, but the first worksheet, the "Master" tab will give them an overview of all underlying worksheets. This is where I will need the percentages filled in. On the tabs "account 1 on" and "account 2 off", I need to know what percentages are Platinum, gold, silver and not to standard. Those cells that should read one of platinum, gold, silver and not to standard are in account 1 on c12, c19, c30 and c41, and in account 2 c15, c23, c34 and c45. This is if you are using the zip file attached to the posting. If you use the savefile.com link, it is D instead of C. I'm not sure if I need a vlookup and countif function, or a different combination.

Question 1 as above: I just realized one of the sources of confusion. The second sheet was not correct. I have included the correct second sheet for account 2 off, which as the formula in C15 I am trying to duplicate.

Here is a very detailed listing of what I need to do in Account 1 on, cell C12:

If 90% of the cells in row 8 have an X AND 100% of the cells in row 9 have an x, then I need the result to read "Platinum", if 80% of the cells in row 8 have an x and 90% of the cells in row 9 have an X, then I need the result to read "Gold", if 70% of row 8 have an x AND 60% of row 9 have an x, the I need it to read "silver", if less than 70% of row 8 have an X AND less than 60% of row 9 have an x, then I need it to read "Not to standard".

For issue #2, for example using Account 2, if I take C13, C20, C31 and C42 they all will (eventually!) read Platinum, GOld, SIlver or Not to Standard. I need to figure out which is the lowest of all four cells. EVen if three are platinum, but one says Not to Standard, then I need cell C49 to show that.

Additionally, disregard the "presence" section, I am waiting on additional parameters for this section.

I hope this helps!

Thanks!!!

File size:
54.3 KB
Views:
18
9. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Real quickly, before I read all of your post, come back and edit yours and delete your email address. You don't need spambots trolling and finding it.

Joined:
Jan 12, 2009
Messages:
24
Thanks!

11. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
emydee, will the sales reps all have Excel 2007 or will some use earlier versions? If it is the latter, you may want to make sure that those items in the Compatibility Report are addressed.

12. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
I'm going to go with 2003. I tried both 2007 and 2003 to see if some of the functionality worked in 2007 that didnt in 2003, but my boss couldn't open the 2007 version, so it was a moot point.

13. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
I might be able to do this naming the ranges and doing it that way.... are you familiar with that method? I have never done it that way before.

14. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
This whole problem should probably be restated better and then we could work back to the formatting of "x" or "" in the cells.
For instance, there are 45 cells in each row. Well, 90% of 45 is 40.5. So do you want 40/45 or 41/45 as the conditional setting?
Also, if these are your conditions
Row| 8 |9 |result
% |90 |100 |Platinum
% |80 |90 |Gold
% |70 |60 |silver
% |>70 |>60 |Not to standard
which is the determining value, the high or the low? If the value in 8 is 90% and the value in 9 is 90%, is it platinum, gold or some alloy?

15. ### emydee23Thread Starter

Joined:
Jan 12, 2009
Messages:
24
It's actually a combination.

In order for it to be Platinum, 90% of row 8 must contain x's AND 100% of row 9 must contain x's.

In order for it to be Gold, 80% of row 8 must contain x's AND 90% of row 9 must contain x's.

In order for it to be Silver, 60% of row 8 must contain x's AND 70% of row 9 must contain x's.

In order for it to be Not to Standard (or not an alloy ), LESS THAN (which I can never tell which way they should go < or >) 60% of row 8 must contain x's AND LESS THAN 70% of row 9 must contain x's.

I wrote the code to do it for row 8, returning "Platinum" for instance, and tried to combine a series of named ranges to void my nesting problem, but get a #value! error.

For just row 8, it works

=IF(COUNTIF('Account 1- ON'!\$G\$9:\$AY\$9,"x")=45-COUNTIF('Account 1- ON'!\$G\$9:\$AY\$9,NA()),"Platinum") .

But when I try to do row 8, and use the 90% parameter, excel converts the =>*0.90 to >=0.9, yet still returns platinum.

=IF(COUNTIF('Account 1- ON'!\$G\$9:\$AY\$9,"x")=45-COUNTIF('Account 1- ON'!\$G\$9:\$AY\$9,NA())>=0.9,"Platinum")

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/789840