Suppose you want to find out how many times particular text or a number value occurs in a range of cells. For example:

If a range, such as A2:D20, contains the number values 5, 6, 7, and 6, the number 6 occurs two times.

If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", "Dodsworth" occurs three times.

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number.

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).

There is something called 'locked reference' in excel which you can use for this, and you use $ symbols to lock a range. For your example, you would use:

=IF(B4<>"",B4/B$1,"")

This locks the 1 in B1 so that when you copy it to rows below, 1 will remain the same.

If you use $B$1, the range will not change when you copy it down a row or across a column.