+ Reply to Thread
Results 1 to 12 of 12

Getting Value with Two Decimal at different places

  1. 03-09-2018, 07:02 AM #1
    gillani77
    gillani77 is offline
    Registered User
    Join Date
    02-20-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Getting Value with Two Decimal at different places

    Hi All,
    I am trying to write a Nested If the formula for more than 7 arguments. Below is my formula and answer received.

    IF(H42<=J38,J39,IF(H42<=K38,K39,IF(H42<=L38,L39,IF(H42<=M38,M39,IF(H42<=N38,N39,IF(H42<=O38,O39,IF(H42<=P38,P39,0)))))))&IF(H42<=Q38,Q39,IF(H42<=R38,R39,IF(H42<=S38,S39,IF(H42<=K40,K41,IF(H42<=L40,L41,IF(H42<=M40,M41,IF(H42<=N40,N41,0))))))&IF(H42<=O40,O41,IF(H42<=P40,P41,IF(H42<=Q40,Q41,IF(H42>=R40,R41,0)))))
    Answer: 6499.49
    As you can see i have two decimalsat two diferent places. This does not allow me to use this value in another formula. CAn someone help me in fixing up my IF formula please.
    Reply With QuoteRegister To Reply
  2. 03-09-2018, 07:20 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Getting Value with Two Decimal at different places

    Not clear about what you are trying to achieve


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 03-09-2018, 07:26 AM #3
    Sonia67
    Sonia67 is offline
    Registered User
    Join Date
    11-19-2015
    Location
    Ireland
    MS-Off Ver
    2010
    Posts
    22

    Re: Getting Value with Two Decimal at different places

    Hi Gillani77, can you attach an example spreadsheet
    Reply With QuoteRegister To Reply
  4. 03-09-2018, 08:26 AM #4
    gillani77
    gillani77 is offline
    Registered User
    Join Date
    02-20-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Getting Value with Two Decimal at different places

    Sonia,
    Please find attached sheet. In Sheet 1! we need to change value and result should come from the table given mentioned in cell J38 to cell R41.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-09-2018, 08:33 AM #5
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,184

    Re: Getting Value with Two Decimal at different places

    No idea what you want!
    Reply With QuoteRegister To Reply
  6. 03-09-2018, 08:38 AM #6
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,184

    Re: Getting Value with Two Decimal at different places

    EDIT: See post #8
    Last edited by JohnTopley; 03-09-2018 at 11:42 AM.
    Reply With QuoteRegister To Reply
  7. 03-09-2018, 09:18 AM #7
    Mazzaropi
    Mazzaropi is offline
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Getting Value with Two Decimal at different places

    gillani77, Good morning.

    If you use the & sign as a function connector the result is TEXT.
    Even if it looks like a number.

    There are no conditions to do calculus with the result obtained.
    Only this!

    I hope it helps.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o
    Reply With QuoteRegister To Reply
  8. 03-09-2018, 09:46 AM #8
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,184

    Re: Getting Value with Two Decimal at different places

    Use this ...


    in J46

    =INDEX($J$39:$AA$39,MATCH($H$42,$J$38:$AA$38,1))

    and change table to be in J38:AA39 as attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-09-2018, 10:48 AM #9
    Sonia67
    Sonia67 is offline
    Registered User
    Join Date
    11-19-2015
    Location
    Ireland
    MS-Off Ver
    2010
    Posts
    22

    Re: Getting Value with Two Decimal at different places

    Hi Gillani77,
    Index Match would be your best option, but you could put the cells that you are looking up onto a new sheet and use those in the formula, straight across as John has done and just keep the ones on your sheet for reference - therefore your sheet will look the same. If you had Excel 2007 onwards, you could nest up to 64 IFs without using the &s. I can see that what your formula is doing is giving you the correct price, from the first 7 IFs work fine, it is then also giving you 9.49, which is in column Q, the start of the next 7 Ifs.
    Reply With QuoteRegister To Reply
  10. 03-09-2018, 12:17 PM #10
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,184

    Re: Getting Value with Two Decimal at different places

    An alternative:

    I put your "Fee" table in Sheet2 and named it "Fee_Table"

    You can use


    =INDEX(Fee_table,MATCH($H$42,INDEX(Fee_table,,1),1),2)

    OR simpler

    =VLOOKUP($H$42,Fee_table,2,1)

    See examples in Sheet1 J46/K46
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 03-17-2018, 01:37 PM #11
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Getting Value with Two Decimal at different places

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  12. 05-18-2018, 07:52 PM #12
    gillani77
    gillani77 is offline
    Registered User
    Join Date
    02-20-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Getting Value with Two Decimal at different places

    Thanks John Topley. Please accept my sincere apologies to get back to you with this delay, as I was switched over to another project.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adusting decimal places based on greatest number of places in a series
    By anelson87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2022, 01:05 PM
  2. Is there a way to set 2 decimal places in a cell without typing the (.) actual decimal?
    By JKRIESEL in forum Excel General
    Replies: 2
    Last Post: 12-29-2013, 08:37 PM
  3. [SOLVED] copy number with 3 or more decimal places and paste the actual value as 2 decimal number
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2013, 12:57 AM
  4. [SOLVED] Calculating decimal places in a non decimal format (ie sixes not tens)
    By Mike Brewer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-07-2013, 08:58 AM
  5. Decimal issue while sum numbers with two decimal places only
    By anis.pt in forum Excel General
    Replies: 4
    Last Post: 12-26-2012, 05:37 AM
  6. Paste two decimal number in excel without extra decimal places appearing
    By jeffery_frick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 07:49 PM
  7. Removing decimal point while maintaining # of decimal places
    By pipermj in forum Excel General
    Replies: 4
    Last Post: 06-10-2009, 12:50 PM
  8. FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT.
    By SUKYKITTY in forum Excel General
    Replies: 3
    Last Post: 07-06-2005, 09:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1