Results 1 to 14 of 14

Thread: Microsoft excel equations

  1. #1
    Ceroc N.I. Franchise Owner drathzel's Avatar
    Join Date
    Aug 2004
    Location
    Bridport, Dorset!
    Posts
    8,175
    Rep Power
    13

    Microsoft excel equations

    I am doing some accounts and i am trying to do the equation that " if paid in F10 then - J10" example!!! Does anyone knwo what this equation is? i have used it before in my previous job but now i am doing it myself i cant remember!

    Help anyone?

  2. #2
    Registered User Gojive's Avatar
    Join Date
    Aug 2004
    Location
    Staines (if you're not careful)
    Posts
    1,716
    Rep Power
    10

    Re: Microsoft excel equations

    Danielle,

    I'm doing this from memory, so it may be wrong!

    I think what you're looking for, is:

    =if(F10,J10,"")


  3. #3
    Ceroc N.I. Franchise Owner drathzel's Avatar
    Join Date
    Aug 2004
    Location
    Bridport, Dorset!
    Posts
    8,175
    Rep Power
    13

    Re: Microsoft excel equations

    Quote Originally Posted by Gojive
    Danielle,

    I'm doing this from memory, so it may be wrong!

    I think what you're looking for, is:

    =if(F10,J10,"")

    I think its prob right but if you are diong it for a whole column and you want paid or p in it?

    EG
    £
    22 Paid
    33 Paid
    44 Paid
    55
    66
    77
    88

    99 paid
    286 owed

  4. #4
    Registered User Gojive's Avatar
    Join Date
    Aug 2004
    Location
    Staines (if you're not careful)
    Posts
    1,716
    Rep Power
    10

    Re: Microsoft excel equations

    Quote Originally Posted by drathzel
    I think its prob right but if you are diong it for a whole column and you want paid or p in it?

    EG
    £
    22 Paid
    33 Paid
    44 Paid
    55
    66
    77
    88

    99 paid
    286 owed
    Oh good grief!...it's Sunday, and I'm half cut! I can't do that one just now

    If you can wait till tomorrow night (when I'll be back home and have Excel available) and if no one else has fulfilled you in the meantime , then I'll try again

  5. #5
    Ceroc N.I. Franchise Owner drathzel's Avatar
    Join Date
    Aug 2004
    Location
    Bridport, Dorset!
    Posts
    8,175
    Rep Power
    13

    Re: Microsoft excel equations

    Quote Originally Posted by Gojive
    Oh good grief!...it's Sunday, and I'm half cut! I can't do that one just now

    If you can wait till tomorrow night (when I'll be back home and have Excel available) and if no one else has fulfilled you in the meantime , then I'll try again
    Thank you! Have some rep in the meantime

  6. #6
    The Forum Legend
    Join Date
    Nov 2002
    Location
    Nottingham
    Posts
    10,672
    Rep Power
    14

    Re: Microsoft excel equations

    Okie...

    So, there are probably better ways to do it. But, I'd have 2 more columns (probably hidden).

    So:

    22 | p | =IF(B1="p", A1,0) | =IF(B1="n",A1,0)
    33 | p | =IF(B2="p", A2,0) | =IF(B2="n",A2,0)
    ..
    55 | n | =IF(B5="p", A5,0) | =IF(B5="n",A5,0)
    ..

    Then, you can sum both rows independantly.....

    Does this make sense?

    I can send you an example if you want.

  7. #7
    Lovely Moderator ducasi's Avatar
    Join Date
    Feb 2005
    Location
    Glasgow
    Posts
    10,015
    Rep Power
    14

    Re: Microsoft excel equations

    How I'd do it, is have two columns off to the right with something like "=if(f10 = "paid",e10)" and "=if(f10 != 'paid',e10)" in each of them.

    Then I'd sum those two column to get total paid and total owed.

    That make sense?

    Edit: Steve, stop stealing my thoughts!!!!!
    Let your mind go and your body will follow. – Steve Martin, LA Story

  8. #8
    The Forum Legend
    Join Date
    Nov 2002
    Location
    Nottingham
    Posts
    10,672
    Rep Power
    14

    Re: Microsoft excel equations

    Quote Originally Posted by ducasi
    How I'd do it, is have two columns off to the right with something like "=if(f10 = "paid",e10)" and "=if(f10 != 'paid',e10)" in each of them.

    Then I'd sum those two column to get total paid and total owed.

    That make sense?
    Beat you

  9. #9
    The Forum Legend
    Join Date
    Nov 2002
    Location
    Nottingham
    Posts
    10,672
    Rep Power
    14

    Re: Microsoft excel equations

    Quote Originally Posted by ducasi
    Edit: Steve, stop stealing my thoughts!!!!!
    Sorry!

  10. #10
    Registered User Gojive's Avatar
    Join Date
    Aug 2004
    Location
    Staines (if you're not careful)
    Posts
    1,716
    Rep Power
    10

    Re: Microsoft excel equations

    I feel so inadequate now!

    If I was sober and awake though, I feel sure I'd find a better way to help your sheets

  11. #11
    The Forum Legend
    Join Date
    Nov 2002
    Location
    Nottingham
    Posts
    10,672
    Rep Power
    14

    Re: Microsoft excel equations

    Quote Originally Posted by Gojive
    I feel so inadequate now!

    If I was sober and awake though, I feel sure I'd find a better way to help your sheets
    Yeah. But at least you got some rep out of it.

    I just re-wrote her spreadsheet for her. And all I got was kisses...

  12. #12
    Registered User
    Join Date
    Oct 2002
    Location
    Cruden Bay (Aberde
    Posts
    7,053
    Rep Power
    13

    Re: Microsoft excel equations

    Quote Originally Posted by drathzel
    I think its prob right but if you are diong it for a whole column and you want paid or p in it?

    EG
    £
    22 Paid
    33 Paid
    44 Paid
    55
    66
    77
    88

    99 paid
    286 owed
    As everyone says, but a couple more things that may help:

    Select a column of cells {eg C5:C20}, then type in the formula in the first of these {eg =if(B5="P",A5,0)} and hold <ctrl> when pressing return: the formulas will copy themselves into all the selected cells as if the 'drag copy' function is used {ie C10 would contain the formula =if(B10="P",A10,0)}

    If a cell uses a formula to be blank instead of containing a '0' value {eg =if(B10="P",A10,"")} and you have another formula in the row {eg F10} like =C10*1.175, then you will get an error. To resolve this, simply use =SUM(C10)*1.175 and it will ignore any text values.

    To pretty things up, you could use "Conditional Formatting" and use roughly the same formula to highlight all those still to be paid {eg select rows5-20 and go to 'conditional formatting'. Insert a formula =if($B10="P",FALSE,TRUE) then select the format (eg a pink background) and hit OK.}

    You can use 'names' to hold formulas or constants; you could name a cell (eg VAT) and any time the name is mentioned, the value in that cell is used (eg 1.175) so that you could have a formula of =C1*VAT and it would be the same as C1*1.175.
    In a similar way, you could be on cell C5 and insert a name; call it "Paid" and in the bit that says "=Sheet1!$C$5" you simply insert the formula above "=if($B5="P",$A5,0)" {note- lock the column so that the formula can be used anywhere in the row}
    Now in C5 you just say "=Paid" and the conditional formula could be =if(paid>0,FALSE,TRUE)

    {If you deve deeper into this and are using formulas spanning pages and workbooks, there is a limit that requires a work-around; formulas entered in this way normally only work for the sheet they are entered on. If you need the work-around, PM me.}

  13. #13
    The Forum Legend
    Join Date
    Nov 2002
    Location
    Nottingham
    Posts
    10,672
    Rep Power
    14

    Re: Microsoft excel equations

    So Danielle....

    How much of that did you understand??

  14. #14
    Ceroc N.I. Franchise Owner drathzel's Avatar
    Join Date
    Aug 2004
    Location
    Bridport, Dorset!
    Posts
    8,175
    Rep Power
    13

    Re: Microsoft excel equations

    Quote Originally Posted by TheTramp
    So Danielle....

    How much of that did you understand??
    this much

    Quote Originally Posted by gadget
    To pretty things up

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Microsoft word Help!!!!
    By drathzel in forum Geeks' Corner
    Replies: 9
    Last Post: 25th-March-2006, 12:56 AM
  2. If Microsoft redesigned the ipod box
    By Feelingpink in forum Chit Chat
    Replies: 5
    Last Post: 15th-March-2006, 03:23 PM
  3. microsoft outlook
    By Lucy Locket in forum Geeks' Corner
    Replies: 12
    Last Post: 13th-July-2005, 12:43 PM
  4. Another one from Microsoft!!
    By azande in forum Chit Chat
    Replies: 1
    Last Post: 19th-December-2004, 01:31 AM
  5. Microsoft spoofs
    By Sheepman in forum Chit Chat
    Replies: 9
    Last Post: 3rd-October-2003, 10:40 AM

Tags for this Thread

Bookmarks

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
  •