% query

  • Thread starter Thread starter wally
  • Start date Start date
W

wally

I hope you can help. I have posted something similar to this before but
got no response. I'll try again. I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
Thanks for any help.
Wallyb
 
Hi!

This would be really easy if you could enter the names in one cell and the
numbers in another.

As long as the number of dashes is the same for every entry:

=FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Biff
 
Ooops!

Hold on.......
show the following; Johnson---6.
would show Smith----26.

Hmmm......you don't WANT this to be easy do you? <VBG>

Back to the drawing board!

Biff
 
So, what happens if both are equal?

Jones---50
Smith---50

Or, will one ALWAYS be less?

Biff
 
Try this:

This assumes that one will ALWAYS be less than the other:

=IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)

Isn't that a thing of beauty?

Now, don't you think you'd be better off separating the names from the
numbers?

Biff
 
Hi Biff,
If both are equal then nothing or a zero is shown in the c cell. I
can't separate the name from the number because the names and numbers
change each week which means there will be a different name and number
in cells a1 and a2. Next week it may have in a1 Kirby 98, a2 Petro 56,
cell a3 Petro 25. The names, numbers and cells change each week. The
way I gather the name and number now is in one cell. Right now I gather
the name and number from a separate sheet named 1,2,3, etc. On each
sheet in cell b1 is the name, in cell r3 on each sheet is the number. I
would want to show the end result on a different sheet. If you can
devise a formula to accomplish the same thing as I am looking for, feel
free to let me know. I'll try it. As you noted, I tried the formula you
sent me but the names are reversed.
 
Biff, I got it to work, I changed the < sign to > and it works great.
Only one more step, when both names have the same number, a zero comes
up next to a name. Can you fix so only a zero shows up?. Thank you very
much.
 
As per your original post:
cell a1 Jones---56
cell b1 Johnson---45
The final result would look like this; a1 Jones----56, b1 Johnson----45, c1
Johnson----6

Not according to your original post. The formula does EXACTLY what you asked
for.
If both are equal then nothing or a zero is shown in the c cell

If both are equal, to return a 0:

=IF(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,0,IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1))

You keep changing things!

You show examples with 3"-", examples with 4"-", examples with no "-" but a
space, the result should be in C3 then C1, the raw data is in A1 and A2 then
it's in A1 and B1 then it's on different sheets in separate cells.
???????????

Tip when posting looking for help: tell us EXACTLY where things are and tell
us EXACTLY what the data looks like. Try to provide enough representative
samples so that we can get a good "picture" of your problem. It's easy for
you, you have the file right in front of you. We don't! We have to try to
recreate the situtation form your explanation.

Biff
 
Hey Biff, I want to apologize for not following the basic rules of
posting and did not give you all of the specific information when I
first posted. I have learned a lesson on just how to ask for assistance
when I need help. I now realize how much you are at a disadvantage when
the file is in front and I know exactly what I want to accomolish. The
formula you provided works just great, (I hate to sound nitpicky) but
there is one more item you may be able to clear up. It is; when the two
numbers are just one apart (a1 Jones----56 b1 Johnson----55) then cell
c1 shows Johnson----0. I know the zero shows up because it is rounded
down after taking 60% of the difference. Is there a way for it not to
show the name next to the zero, but still show the zero? Again, sorry
for the trouble and thanks for the great assistance.
 
Ok, this is starting to get "unwieldy" !!!!

=IF(OR(MID(A1,FIND("-",A1)+3,255)+0=MID(B1,FIND("-",B1)+3,255)+0,FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1)=0),0,IF(MID(A1,FIND("-",A1)+3,255)+0<MID(B1,FIND("-",B1)+3,255)+0,LEFT(A1,FIND("-",A1)-1),LEFT(B1,FIND("-",B1)-1))&"---"&FLOOR(ABS(MID(A1,FIND("-",A1)+3,255)-MID(B1,FIND("-",B1)+3,255))*0.6,1))

You're approaching "Monster formula" status and this thing doesn't even have
any error checking in it!

Biff
 
Hi,
I agree, this is getting past monster stage. I'll go with what you gave
me, it's working just great. Thanks again for your help. Wally
 
You're welcome. Thanks for the feedback!

Biff

wally said:
Hi,
I agree, this is getting past monster stage. I'll go with what you gave
me, it's working just great. Thanks again for your help. Wally
 
Back
Top