Finding a valid cell in a row

[Charles]

I have a spreadsheet with rows of employees and columns of months in the year
for five years. Each month is actually three columns "Salary", "Hours", "%".
There is only an entry when a salary changes. I need to be at one "%" and
look back down the row to find the previous salary so that the percentage
increase can be calculated. How do I find that previous salary. You can
assume the hours are always less than 40 and the salary greater than 100.
This is an example of the spreadsheet:

1 A B C D E F G H I J
K L
2 Mth1 Mth2 Mth3 Mth4
3 Name Salary Hrs % Salary Hrs % Salary Hrs % Salary Hrs %
4 Aa 500 39 550 39
5 Bb 600 35 695 35

Thanks

Charles

 

[Franz Verga]

I have a spreadsheet with rows of employees and columns of months in
the year for five years. Each month is actually three columns
"Salary", "Hours", "%". There is only an entry when a salary
changes. I need to be at one "%" and look back down the row to find
the previous salary so that the percentage increase can be
calculated. How do I find that previous salary. You can assume the
hours are always less than 40 and the salary greater than 100. This
is an example of the spreadsheet:

1 A B C D E F G H I
J K L
2 Mth1 Mth2 Mth3
Mth4 3 Name Salary Hrs % Salary Hrs % Salary Hrs % Salary Hrs
% 4 Aa 500 39 550 39
5 Bb 600 35 695 35

Thanks

Charles

Hi Charles,

you can use this formula in G4 (the % cell for month2) and then copy across
the rows and also in the % cells of next months.

=IF(AND(E4<>B4,B4<>""),(E4-B4)/B4,"")

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy