//some 3rd party need geo

Excel formula

  Savannah 22:31 01 Apr 03
Locked

I have 1 page with a different exchange rate for each month of the year.
On the other sheet I have 1 column with the date and another with an amount of cash.
I would like to make an automatic formula that says if the date = Jan etc then calculate the amonut x the exchange rate. I have found the excel only allows 7 if rules.
Anybody got a solution

  tbh72 22:36 01 Apr 03

Ummmm can you create a named table in a seperate area of your sheet, then use a hlookup to get the correct exchange rate for your calculation.

Just a suggestion?

  rasalom 00:57 02 Apr 03

if you use the name box to name all the different exchange rates (eg January's exchange rate, etc) on page 1 then you can get round the 7 nested IF statesments rule by referencing the named areas (and not the months cell references) in your IF statement on page 2.

Hope this makes sense.

  cherria 09:32 02 Apr 03

on page 1 (lets assume its called 'Sheet1') assuming you have the month in column A and the rate in column B and that you have column titles in row 1. Rather than putting just 'January' in column A, put 1-jan-2003 and instead of 'February' put 1-feb-2003 etc. then format this using a custom format as mmmm.

What you then get is just the month displayed but the value in the cell is actually a date representing the 1st of each month.

Next, on sheet 2 assuming you have column titles in row 1 and that month is in column A and the amount of cash in column B, put this formula in cell C2

=VLOOKUP(a2,sheet1!$A$2:$B$13,2,TRUE)

this assumes there are 12 values, 1 per month?

copy this formula down for every cash value you have and that should do the trick.

This thread is now locked and can not be replied to.

What is Google Allo? What is Google Duo? Google Allo UK release date rumours and features: Google…

1995-2015: How technology has changed the world in 20 years

These clever designs help visualise a complex intelligence tool

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…