Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel 2003 Formula Error

  • 27-01-2015 6:35pm
    #1
    Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    I have a Total cell on an Excel spreadsheet which contains the following formula

    =IF(SUM(G205)>0,(G205+G206+G207),"""")

    It works fine with one exception, if the cell G207 is empty (no value) the Total cell returns a Value error.

    There will be occasions where cell 207 will be empty so what should the above formula be to allow for that?

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



Comments

  • Registered Users, Registered Users 2 Posts: 2,831 ✭✭✭Healio


    Could you put a 0 in g207?


  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    I don't have Excel 2003,so can't guarantee that this will work, but something similar should.

    Replace (G205+G206+G207) in your formula with
    IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206))
    which will add only 205 and 206 if 207 does not contain a number.


  • Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    Healio wrote: »
    Could you put a 0 in g207?

    Nice idea Healio but it won't work that way ;)

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • Posts: 0 CMod ✭✭✭✭ Ellie Flabby Bun


    i'd wrap the whole thing in an if(iserr( to filter out g207
    if(iserr(IF(SUM(G205)>0,(G205+G206+G207),"""")),IF(SUM(G205)>0,(G205+G206)),IF(SUM(G205)>0,(G205+G206+G207),""""))


  • Registered Users, Registered Users 2 Posts: 818 ✭✭✭Triangla


    Try this:

    =IF(G205>0,G205+G206+G207," ")


  • Advertisement
  • Posts: 0 CMod ✭✭✭✭ Ellie Flabby Bun


    actually why are you summing one cell?
    you can replace that with if(g205>0)


  • Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    I don't have Excel 2003,so can't guarantee that this will work, but something similar should.

    Replace (G205+G206+G207) in your formula with
    IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206))
    which will add only 205 and 206 if 207 does not contain a number.

    Top marks MatureStudent, your formula just needed one extra bracket at the end and it worked perfectly. Really appreciate the help ;)

    IF(ISNUMBER(G207),SUM(G205:G207),SUM(G205:G206)))


    Can I trouble you with one last issue? I forgot about one more cell that needs to be included in the Total cell formula but it too may at times be empty, it's cell A15

    Can you adjust the above formula to include cell A15 under those conditions?

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • Posts: 0 CMod ✭✭✭✭ Ellie Flabby Bun


    Didn't see MS' post - much less clumsy than mine :D


  • Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    Triangla wrote: »
    Try this:

    =IF(G205>0,G205+G206+G207," ")

    No, that doesn't work either unfortunately.

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    Sorry about the ), I forgot about the one at the start.
    Is there any relationship between a15 and G207 - could they both be blank at the same time?


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    Sorry about the ), I forgot about the one at the start.
    Is there any relationship between a15 and G207 - could they both be blank at the same time?

    Yes both could be blank at the same time. In fact both will either have a value or both will be blank, there will never be an occasion where one will have a value and the other not. Maybe if I explain their functions

    Cell A15 contains a figure

    Cell G207 contains a percentage figure of A15 (a VAT Rate calculation)

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    Replace SUM(G205:G207) with (SUM(G205:G207) + a15) which will also add a15 any time it finds a number in G207.


  • Registered Users, Registered Users 2 Posts: 8,444 ✭✭✭Gadgetman496


    Replace SUM(G205:G207) with (SUM(G205:G207) + a15) which will also add a15 any time it finds a number in G207.

    Which makes the formula

    =IF(SUM(G205)>0,IF(ISNUMBER(G207),(SUM(G205:G207) + A15),SUM(G205:G206)))

    And it works perfectly now.

    Your assistance is greatly appreciated, thank you :)

    "Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid."



  • Registered Users, Registered Users 2 Posts: 33 MatureStudent


    No problem, glad it worked.


Advertisement