Help with IF (again)

[philiphales]

I have 4 columns

Base Data; Better than Base; Even better Data; The Best Data: TOTAL

In the TOTAL column I need to insert info - if the only data is Base then Base, if the data is Better than Base then Better, if the data is Even Better then Even Better, if The Best then The Best.


ie

Base Data; Better than Base; Even better Data; The Best Data: TOTAL
75,000..............................................................................75,000
79,000................................80,000.....................................80,000
30,000.........................................................26,000............26,000


Thanks

[oldchippy]

If you are looking for the last entry in the row, then

=LOOKUP(1,1/(A2:D2<>0),A2:D2)

[philiphales]

This works, but only if there is data to the immediate LEFT. If I have Base Data then The Best - It only gives me Base Data.
ie if there are any blanks if ignores what is RIGHT of the blank!

[oldchippy]

Are you looking for the maximum figure?

=MAX(A2:D2)

[philiphales]

We have Base Data - as time progresses we get Better Data (moving left to right) however, this sometimes jumps a review and can go from Base Data to The Best Data. If that makes sense!

Thanks

[oldchippy]

So what you are looking for is the last actual "entered" data in that row?

[philiphales]

Yes - I guess that is true. I started with [If] a cell had data then data else move left and so on, but it did not work. I did not think about lookups.

[Carim]

Hi,

Do you mean you are after the latest processed input in a given row ...?
i.e. irrespective of amount or location ...

Carim

[philiphales]

I have 4 columns of data ranging from old (left) to newest (right).

Not ALL columns will be populated. I need in the TOTAL COLUMN (most right) the latest figure - be it from Col 1; 2; 3; or 4.

However the data in the ROW might have the latest data in Col 1; 2; 3; or 4, but NOT necessarily in every Col. It depends when the Data was obtained and this info is in the Col header.

Look again at my initial post

Hope this helps.

I can email the spreadsheet!!!

[Carim]

Yes please post a zipped copy of your worksheet ...

When you say the latest, how it that identified ...?
Do you time stamp each input ? ...

Carim

[Carim]

Hi,

Attached is a sample for a possible solution ...
Does it answer your question ...?

HTH
Carim

[philiphales]

Thanks for your help - please see revised sheet.

[Bryan Hessey]

Thanks for your help - please see revised sheet.

HI Carim,

you can replace
Target.Offset(0, 6).Value = Now
Target.Offset(0, 6).NumberFormat = ";;;"

with
Cells(Target.Row, 5).Value = Target.Value

and save 4 columns (which will not work if the OP uses one of them)

hth
---

[Carim]

Hi,

Attached is your corrected worksheet ...

If I may you were looking for the last value in row ... and oldchippy had properly answered your question, a while ago ...

HTH
Carim

[philiphales]

Carim

Your help was greatly appreciated on this. However, your formula does differ from 'oldchippys' in as much as yours is =Lookup(2,1 and his was =Lookup(1,1 which meant that if there was a gap in the data (empty cell) it did not read the rightmost column. Anyway thanks to oldchippy too.

I had not considered lookup, so will have to do some research into lookup as it may help me in the future.

I was telling my Partner that in the late 80's when using Lotus 1-2-3 the help file was CRAP and if you could not do something that was it - you were stuffed. Without this magnificant help we would still be there today, so a big pat on the back to ALL those who are willing to give up their time to help us mere mortals.

Regards Philip

[Carim]

Glad your problem is fixed.
I am sure you know... we are all mortals ...!!!


Thanks for the feedback
Carim