+ Reply to Thread
Results 1 to 6 of 6

Do a certain calculation if a criteria is met

  1. #1
    Registered User
    Join Date
    11-17-2020
    Location
    UK
    MS-Off Ver
    2016
    Posts
    21

    Do a certain calculation if a criteria is met

    Hello all!

    I will need some help. So, in my excel worbook there is a table with some ranges and amounts; there's a variable that can be changed by the user (E11) and a result that I would like to calculate with vba (E12).

    The user of the workbook will only be allowed to change the cell E11 and, based on this cell, a calculation in E12 will be done automatically (with VBA, without the need of pressing a button).

    I've attached the excel workbook.

    The calculation would be the following:
    If the value in yellow (E11) is lower than > 10.000, then E12= 0
    If the value in yellow (E11) is > 10.000 and <20.000 , then the formula should be E12=E11*D5 -E5
    If the value in yellow (E11) is > 20.000 and <30.000 , then the formula should be E12=E11*D6 -E6
    If the value in yellow (E11) is > 30.000 and <40.000 , then the formula should be E=12E11*D7 -E7
    If the value in yellow (E11) is > 40.000 and <50.000 , then the formula should be E12=E11*D8 -E8
    If the value in yellow (E11) is > 50.000 then the formula should be E12=E11*D9 -E9

    Anyone could help me?

    Thank you very much.
    Last edited by caet_; 12-30-2021 at 08:32 AM.

  2. #2
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,163

    Re: Do a certain calculation if a criteria is met

    E.g
    Please Login or Register  to view this content.
    The above code should be placed in the sheet module (e.g. Sheet1), not in the standard module (e.g. Module1).

    In the description you did not define the ranges to which the limit values belong (10k, 20k etc.). I assumed that they are the lower limit of the next range.

    Artik

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Do a certain calculation if a criteria is met

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Do a certain calculation if a criteria is met

    Why do you need VBA?

  5. #5
    Registered User
    Join Date
    11-17-2020
    Location
    UK
    MS-Off Ver
    2016
    Posts
    21

    Re: Do a certain calculation if a criteria is met

    Quote Originally Posted by Artik View Post
    E.g
    Please Login or Register  to view this content.
    The above code should be placed in the sheet module (e.g. Sheet1), not in the standard module (e.g. Module1).

    In the description you did not define the ranges to which the limit values belong (10k, 20k etc.). I assumed that they are the lower limit of the next range.

    Artik
    Thank you very much! It was exactly what I wanted.

  6. #6
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,163

    Re: Do a certain calculation if a criteria is met

    But I will repeat like an echo:
    Quote Originally Posted by TMS View Post
    Why do you need VBA?
    Artik

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Xirr - Calculation with Criteria
    By carlbrianhadi in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-31-2018, 01:16 PM
  2. [SOLVED] If And calculation with 3 criteria
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2015, 09:06 AM
  3. [SOLVED] Multiple Criteria Calculation
    By pandora1a2b in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2015, 10:52 AM
  4. Bonus calculation with certain criteria
    By mbeebash in forum Excel General
    Replies: 7
    Last Post: 03-17-2015, 09:45 AM
  5. SLA Calculation with criteria
    By dwhite30518 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2015, 11:27 AM
  6. Calculation based on a criteria
    By hoosier5 in forum Excel General
    Replies: 3
    Last Post: 10-25-2011, 10:40 AM
  7. Criteria based calculation help.
    By rosarionyc in forum Excel General
    Replies: 18
    Last Post: 11-19-2010, 05:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1