Danielle,
I'm doing this from memory, so it may be wrong!
I think what you're looking for, is:
=if(F10,J10,"")
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?
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?Originally Posted by Gojive
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 nowOriginally Posted by drathzel
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 meantimeOriginally Posted by Gojive
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.
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
Beat youOriginally Posted by ducasi
Sorry!Originally Posted by ducasi
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.Originally Posted by Gojive
I just re-wrote her spreadsheet for her. And all I got was kisses...
As everyone says, but a couple more things that may help:Originally Posted by drathzel
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.}
So Danielle....
How much of that did you understand??
this muchOriginally Posted by TheTramp
Originally Posted by gadget
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks