how to report a value using IF function without performing any math operations

**rippa** · 01-28-2011, 10:28 PM

Hey everybody,
I am fairly new to excel,If ANYONE CAN HELP ME WITH THIS FORMULAR I WILL GREATLY APRECIATE IT!!! Thanks in advance

I am ready to kill myself, as it should be sooo easy and yet I cannot make this formular work.
Here is how my spread sheet looks like
A B C
1 RMSE Pea 0.97
2 ME Pea 0.56
3 RMSE Brown 0.51
4 ME Brown 0.76

this spread sheet is 200 rows, so I needed to created another table summarizing ME and RMSE for each author from column B.

A B C
6 MRSE ME
7 Pea
8 Brown

I guess what I am trying to accomplish is: if RMSE and Pea then repoprt 0.97
her is my function and it works for the first row only and returns FALSE for the rest

=IF($A$1:$A$4=$B$6,IF($B$1:$B$4=$A7,$C$1:$C$4,"")

I know I can find may wary around by using sumif function in this case, but I am struggling with this format when is do not need to do any math operation but only report a cell
I also have to use nested array b/c I'll be sorting this data later

**teylyn** · 01-29-2011, 03:52 AM

Hello,

you could use a pivot table. Put the data from column A into the column labels, the author from column B into the row labels and the value into the values.

See attached.

To get started with pivot tables, check out http://peltiertech.com/Excel/Pivots/pivotstart.htm

cheers,

**rippa** · 01-29-2011, 01:15 PM

Hello, thank you soooo much for your help. Hopefully one day I can get to the level when I can give a helpful excel advise to other ppl.

This is a great idea, but it looks like I cannot sort the values in pivot table. Maybe I simplified my question too much. But the bottom line is that I have created a master file so I can analyse the results base on several criterias; I need the sorting feature to be able to see the which groups of studies match my selection criteria.

I have column A B C: if one of the cells in column A = x, and if the cell from the same row in column B = y, the report the cell from the same row in column C to column D.
Please see the attachment

If you do not have time, could you please at least point me in the right direction

Once again, thank you for volunteering your free time to help me

**scottylad2** · 01-29-2011, 02:18 PM

SUMPRODUCT(--(ISNUMBER(SEARCH($A$2,$A$4:$A$6))),--($B$4:$B$6=$B$2),$C$4:$C$6) will draw the number from column C where both criteria are met, 1st and Correct in your sample 0.914

An extra thought, if there is more than one row where the criteria is met the above will summ the totals of both rows, or all rows that meet

**rippa** · 01-29-2011, 06:09 PM

Hey scottylad2,
the formular works great except the part that when more than 2 rows match selection criteria, it sums the numbers from corresponging rows. Any thought on how to avoid this?

**scottylad2** · 01-30-2011, 02:05 PM

=IF(AND(ISNUMBER(SEARCH(A$2,A4)),B$2=B4),C4,"wrong")

This in D4 and dragged down. It will then give you a line by line answer, is that what you need? For example, if you have a match on the 1st and 2nd rows, do you want to display the values in C for both rows individually? and if thats the case would row 3 (a non match) display wrong? if thats correct this formula will do that. If not, please alter your sample to show what you would like displayed if there's a match on more than one row

hope this helps

**rippa** · 01-31-2011, 12:25 AM

scottylad2, Thanks a lot this formular worked great!!! I also was able to add OR condition to it... really proud of myself and appreciate your help.

**rippa** · 01-31-2011, 01:31 AM

don't wanna seem ungrateful but is it possible to use this formular to search values which are < than selection criteria; e.g when values in colum A are less than $A$2

**scottylad2** · 01-31-2011, 07:23 AM

Originally Posted by rippa

don't wanna seem ungrateful but is it possible to use this formular to search values which are < than selection criteria; e.g when values in colum A are less than $A$2

whats all the possible values you might have in A2? and if not 1st, say 2nd what do you require as a result? please attach a fresh sample with a before and desired after

**rippa** · 02-01-2011, 01:41 AM

Oh sorry, thanks for reminding me to describe the task more clearly.

So I was wandedring how to report values in column C that are < than $C2. Need to be able to sort this column also.
e.g. IF (C$4:C$8<$C$2,"YES","") except this formular works only for the frist line.

Actually, what is the deal with IF function. Why cant I just report a cell?

how to report a value using IF function without performing any math operations

LinkBack

Thread Tools

Rate This Thread

Display

how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Re: how to report a value using IF function without performing any math operations

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions