+ Reply to Thread
Results 1 to 11 of 11

Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

  1. #1
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Hi Guys,

    In the attached file in columns G, H, I & J, I have tried to use combinations of the above functions. I have arrived at the desired results for each with the exception of H9 & I11. These relate to columns C & B which are blank, I need the two blanks (H9 & I11) to each become 1.00.

    Also, alhough in the main columns G, H, I & J are giving expected results, are the formulas that I have "concocted" acceptable and robust?

    There are more notes on the worksheet.

    TIA
    Keith
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    There are other blank cells in C and B. What is distinctive about C9 and B11 ... or for that matter H9 and I11 that H9 and I11 should return 1?
    Dave

  3. #3
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Hi Dave,

    I knew I was going to struggle to explain this, but here goes.

    If B,C,D & E are blank, then G,H,I & J should also appear blank.

    If B has a value and C is blank, then I would contain E (RoundedUp) and H should be 1.
    If B is blank and C has a value, then H would contain C (RoundedUp) and I should be 1.

    The data on the right of the example is to produce a CSV file that mus not contain zero values.

    Thanks for you time,
    Keith

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    This is a bit confusing. Is this it??

    H7: =IF(J7="","",IF(C7="",1,ROUNDUP(ABS(C7),0)))

    I7: =IF(J7="","",IF(E7="",1,ROUNDUP(ABS(E7),0)))

    J7: =IF(SUMPRODUCT(LEN(B7:E7))=0,"",IF(C7<0,16,10))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Here's what I came up with:

    G7: =IF(AND($B7="",$C7=""),"",A7)
    H7: =IF($C7="",IF($B7="","",1),ROUNDUP(ABS($C7),0))
    I7: =IF($B7="",IF($C7="","",1),ROUNDUP(ABS($E7),0))
    J7: =IF($D7="","",IF($D7<0,16,10))

    Not sure about column J though; especially J9 because D9 is blank.

    WBD
    Attached Files Attached Files
    Office 365 on Windows 11, looking for rep!

  6. #6
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Hi Glen,
    Thanks for your help.
    You say "This is a bit confusing. Is this it??"

    If it's confusing for you, I have no chance. I know what I want, but explaining it is as hard as the formula.

    I will try to write this very longhand

    H7: =IF(B7 and C7="","",IF(B7 isblank C7=1,If(B7 and C7 both have value,ROUNDUP(ABS(C7),0)))

    Same for I7
    I7: =IF(B7 and C7="","",IF(C7="",then I7=1,If(B7 and C7 both have value,ROUNDUP(ABS(E7),0)))

    I don't understand the SUMPRODUCT as I have never come across it.
    J7: =IF(B7 or C7 are negative) J7 = 16 else J7 = 10

    I hope this helps,
    Keith

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    It would be easier if you would LOOK at the results in the sheet that I posted and TELL me which ones are incorrect (and obviously, if incorrect what the correct values should be).

  8. #8
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Column J should be dependant on both columns C & D.

    If C & D are blank then J should be blank.

    If either C or D are negative then J should be 16.

    If C OR D is blank (the other has a number) then J should be 10

    I Think.

  9. #9
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Ok. How about:

    J7: =IF(AND($B7="",$C7=""),"",IF(OR($C7<0,$D7<0),16,10))

    WBD
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    Ok sorry Glen,

    J8 should be 16 as there is a neg figure in B8

    Row 9 & 11 are showing the correct result, but if a neg figure is entered into B, then J should become 16

  11. #11
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: Combining formulas with If, IsBlank, ABS, Isnumber & RoundUp

    To Glen & WBD

    I will mark this post as solved as I think that the solution that WBD has supplied has solved the problem.

    Thank you both for all the effort you have put in.

+ 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. Combining MAX and ROUNDUP
    By thewiseguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-15-2018, 07:28 AM
  2. [SOLVED] Combining COUNTIFS and ISNUMBER
    By OverKnight in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-05-2015, 12:55 AM
  3. [SOLVED] ISNUMBER and ISBLANK
    By RMontani in forum Excel General
    Replies: 1
    Last Post: 05-05-2012, 03:52 PM
  4. Combining MAX, ROUNDUP & IF for the same cell
    By babydee0413 in forum Excel General
    Replies: 7
    Last Post: 02-22-2010, 03:13 PM
  5. Check cell for data. Tried ISREF, ISBLANK, ISNUMBER, ISTEXT.
    By giallofever in forum Excel General
    Replies: 5
    Last Post: 03-24-2009, 02:22 PM
  6. combining 2 formulas markup roundup
    By bens993 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2007, 06:20 PM
  7. Data Validation using IsNumber and IsBlank
    By Gos-C in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2007, 07:28 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