I'm having a problem with #Value. I've imported some historical price data from Bloomberg in column A and B.
A B C
ticker x ticker y ticker y - ticker x
1 #N/A N/A #N/A N/A #VALUE! (#N/A N/A)
2 93.707 #N/A N/A #VALUE! (93.707)
3 #N/A N/A 790 #VALUE! (-790)
4 93.375 790.5 -697.125
5 93.707 795.013 -701.306
6 92.504 803.085 -710.581
The problem is with cells C1, C2 , and C3.
I need to do some further analysis with the data in column C, but because it returns as a #Value, I can't do them. I need the data in Column C to either be numerical or #N/A N/A. I know that if I use =SUM(A2,B2) my data in C3 will return the value in A2 (93.707), but i need to do a subtraction. There is no DIFFERENCE or MINUS function (similar to SUM) that I'm aware of and I can't multiply column A or B by -1 as this will still return #Value as an answer in Column C. any ideas on how i can achieve the results i'm looking for? i've put the answer in parenthesis for waht i'm trying to achieve.
Thanks
dw
Last edited by dave42100; 01-09-2012 at 04:34 PM.
the data came out pretty poorly.
A1 = #N/A N/A
A2 = 93.707
A3 = #N/A N/A
A4 = 93.375
A5 = 93.707
A6 = 92.504
B1 = #N/A N/A
B2 = #N/A N/A
B3 = 790
B4 = 790.5
B5 = 795.013
B6 = 803.085
C1 = #VALUE! (Want data to return #N/A N/A)
C2 = #VALUE! (Want data to return 93.707)
C3 = #VALUE! (Want data to return -790)
C4 = -697.125
C5 = -701.306
C6 = -710.581
Hello
Try this formula:
It's not very elegant but it seems to work. Maybe someone can come up with something more concise.=IF(AND(ISTEXT(A1),ISTEXT(B1)),"#N/A N/A",IF(AND(ISNUMBER(A1),ISTEXT(B1)),A1,IF(AND(ISTEXT(A1),ISNUMBER(B1)),B1*-1,B1-A1)))
Hope this helps.
Try this,
=IF(COUNTIF(A1:B1,"<9E300"),SUMIF(A1,"<9E300",A1)-SUMIF(B1,"<9E300",B1),NA())
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
thanks for for the help this works.
this also. thks for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks