I'm going to try and explain this the best I can, I'm new to the forum. I spent my entire saturday night trying to figure out these formulas with no luck. attached is two sample worksheets that have sample data I'm trying to formulate. there are three different formulas I can't figure out.
in sheet 2 - column H - the instructions are clear, I tried using IF nesting, but i'm assuming it wouldn't work because the cells in column F, and G, actually have VLOOKUP formulas that look like "=VLOOKUP(E4,A4:B3288,2)" for the actual worksheet cell instead of just the numbers, I typed in the numbers to make the sample. What other formula can I use that will work with the Vlookup formula in the cell and still give me a true or false, for two IF problems.
also, in sheet 2 - column J - can I create a Vlookup to tell me the Median of all the numbers in column C, if the number in Column B is greater than 1. I have no idea how to even start to type that.
finally, sheet - column F - I have a SUMIF forumla there now, but it is counting all the "A"s instead of just the first 5, I need a formula that can recognize the unique symbol in column E and count how many times it appears in Column A that can be copy and pasted down the whole list because there are actually 800+ unique symbols.
this is about the best I can explain my problems. any help would so appreciated and if you have any more questions I'll be checking back all day.
Last edited by musicmedicine; 06-14-2009 at 03:02 PM.
This is some sort of homework assignment, right?
In that situation members here should only really give you pointers or advice, not full answers.
What have you tried so far?
yeah it's actually an assignment by a potential employer, they said I could find any help on the internet if needed, but yeah any assistance would be awesome, not looking for a hand-out. in column H sheet 2 I tried
=IF(AND(F4<1.5,G4>5),"yes","no") but it gives me all "no" when there are some that are yes, however the formula seems to work, when there are just numerals in the cells, not vlookup formula's.
for sheet two column J I have tried doing an equation like - =IF(B4:B3288>1,MEDIAN(C4:C3288))
but I know there needs to be a Vlookup or something like it because it needs to find the numbers than are >1 in column be then find the median of the corresponding numbers in column C
and for sheet 1 - I tried
=SUM(COUNTIF(A4:A3797,{"*E5*"}))
where E5 is the unique symbol that I would like to copy to E6, E7, E8, etc. when I put "A" in for E5 it counts all the A's not just the first 5 I want it to count.
so far this is what I've achieved. thanks for any assistance
OK,
For the first one you are almost there. The fact that the values in the cells are from formulas makes no difference as long as the values returned are numeric. You have the right syntax, i.e.
=IF(AND(F4<1.5,G4>5),"yes","no")
but 5 isn't the same as 5%
For the Median you need the MEDIAN function to be outside the IF like this
=MEDIAN(IF(B4:B3288>1,C4:C3288))
This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER
I can't believe I was that close both times, Excel has a way of making you feel so dumb.
for the sheet 1 formula, am I close? there is just something missing.
thank you so much for your help so far, the time I spent last night going nuts over this is unbelievable.
at H3:
=IF(AND(F3<1.5,G3>5%,"yes","no")
at J3:
with Ctrl+Shift+EnterPHP Code:=MEDIAN(IF(B3:B3288>1,(C3:C3288)))
oh, dadylonglegs had helped you, I was so late,
Last edited by tigertiger; 06-14-2009 at 02:36 PM.
Originally Posted by musicmedicine
at F4:
=COUNTIF($A$4:$A$3797,E4)
Copy down for F5:F...
you are my savior!!!! I cannot believe it was that easy!!!! thank you so much
I can't thank you enough.
I'll mark the problem as solved, and TigerTiger, I'll give you reputation points.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks