+ Reply to Thread
Results 1 to 11 of 11

Use Percentage otherwise a fixed amount... IF

  1. #1
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Use Percentage otherwise a fixed amount... IF

    Hello everyone,

    This is driving me nuts so can someone help, please? lol

    This is what I'm trying to achieve as a function in a cell, which has a (£) figure being calculated into it.

    3% of purchase or 4K whichever is greater

    So, for example...

    The figure can never go below £4K, but if the [cell containing purchase price] works out to be more than 4K based on 3% of [cell containing purchase price] use the 3% figure it will be greater than 4K.

    Hope I explained that correctly lol

    Many thanks in advance from a newbie

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Use Percentage otherwise a fixed amount... IF

    This perhaps?

    =MIN(A2*3%,4000)

  3. #3
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Re: Use Percentage otherwise a fixed amount... IF

    Hi Bob, nope - it didn't work (but many thanks) I've hopefully uploaded an example sheet after my initial post. I've just used your formula and added £600, 000 to the [Purchase Price cell] 3% of that figure is 18,000 which is greater than 4K, but instead, your calc used the 4K. Many thanks, Robert

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Use Percentage otherwise a fixed amount... IF

    Nothing attached Perhaps
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BTW is this a Google sheet question?
    Last edited by Pepe Le Mokko; 11-02-2020 at 11:35 AM.

  5. #5
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Re: Use Percentage otherwise a fixed amount... IF

    Hopefully, I've now added an attached spreadsheet that should help. Many thanks Robert
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Re: Use Percentage otherwise a fixed amount... IF

    It is a Google sheet question yes, but I'm currently on a MAC and don't want to open my PC laptop. I hope that's OK? If not, I will open the PC laptop but I'd expect there to be few differences in the formula regardless.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Use Percentage otherwise a fixed amount... IF

    Like
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ?

  8. #8
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Re: Use Percentage otherwise a fixed amount... IF

    Many thanks Pepe, but it the change didn't seem to make any difference

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Use Percentage otherwise a fixed amount... IF

    I don't know about Google sheets. Please post in the correct forum in the future. Cheers

  10. #10
    Registered User
    Join Date
    11-02-2020
    Location
    Coventry
    MS-Off Ver
    google speadsheet
    Posts
    7

    Re: Use Percentage otherwise a fixed amount... IF

    OK sure, but I'm still no further forward. I've just used the formula suggested using Excel and it doesn't work

    Can someone take a look at the attachment posted above which shows what I'm trying to do? Many thanks, everyone

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Use Percentage otherwise a fixed amount... IF

    For the file attached to post #5 try the following in cell B3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Percentage Markup or Fixed Amount
    By Phillip343 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2020, 10:27 PM
  2. Formula required for a fixed fee amount
    By cool_eyes99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-17-2017, 02:36 AM
  3. Replies: 2
    Last Post: 11-21-2014, 09:35 AM
  4. Getting sum total from percentage instead of percentage amount
    By Bluedemon909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2013, 08:56 AM
  5. Reduce cells by fixed amount
    By rcm1946 in forum Excel General
    Replies: 1
    Last Post: 07-26-2011, 10:45 PM
  6. Replies: 1
    Last Post: 03-03-2010, 05:26 AM
  7. How to add a fixed amount to all cells in a column
    By ste5442 in forum Excel General
    Replies: 1
    Last Post: 06-30-2008, 08:04 AM

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