Excel frustration with multiple IF statements

The image is very tiny and difficult to read, but I am guessing from what I can make out that a LOOKUP or VLOOKUP statement would be better for you. Post your formula with spaces either side of the < and > symbols so that the Sucuri firewall doesn't kick in.

No, sorry - I'm not jumping through hoops. I have explained how to place the formula here so that (a) it's easy to read and (b) anyone wanting to help can copy and adapt it for you. Or, you could attach the workbook.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Ok I attached the Excel sheet I've been working on, added in some fake data and removed anything that was irrelevant. Here is the breakdown:

Anything highlighted in Grey is changeable data fields that need to be used in the calculation for "Gross Monthly Revenue"
Anything under the Orange banner Fields C3:H12 are fixed prices that need to be used in the calculation for "Monthly Fees"
Anything highlighted in blue are the total number of subscribers for that particular service and is used in both calculations
Disregard anything highlighted in Purple

Fields B3:B4, B7:B8, B11:B12 are the MSRP pricing set by the customer.
Fields B21:B22, B25:B26, B29:B30 are the total subscribers for that particular service.

Calculations needed:
Gross Monthly Revenue: MSRP pricing X the # of subscribers for all 6 products but only if the total number of subscribers is equal to or greater than 1000 for that particular service. EX: B3xB21 + B4xB22 + B7xB25 + B8xB26 + B11xB29 + B12xB30.

Monthly Fees: This is more complicated. This calculation needs to find the appropriate "Volume discounted price" based on the number of subscribers, multiply that number to the number of subscribers, do this for all 6 fields and then total it all but it should only do the calculation if the total number of subscribers is equal to or greater than 1000 for that particular service.

Net Monthly Revenue: "Gross Monthly Revenue - Monthly Fees"


Let me know if I need to clarify anything and I appreciate all the help.

Where are the total number of subscribers??

So. I'm not 100% certain this is what you're after, but I think it is.

Formula:

Please Login or Register  to view this content.

From your last post, this seems to be what you are after:

=IF(B7 < 1000,"Minimum Not Met",B7*LOOKUP(B7,{1000,25000,50000,150000,300000,500000},{9,8,7,6,5,4}))

AliGW, closer, but not yet there. I think.

Digital....

1. I assume that in your post the A17 /18 etc are not accurate and should really read as shown here (in the file). Yes or No?

2. Do you want the discount criteria applied at the PRODUCT level (eg row 16) or at the customer category level (e.g. separately at rows 17 & 18).

Is this closer (some rearrangements in yellow)??

Final effort. Does this do it? I may have added the wrong attachment a bit earlier up this chain... apologies for any confusion caused.

You're welcome! :0

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.