Cabaran formula - perbezaan dari entri terakhir - Teka-teki

Isi kandungan

Konteksnya

Beberapa minggu yang lalu, saya mempunyai soalan menarik dari pembaca mengenai mengesan kenaikan atau penurunan berat badan dalam jadual sederhana.

Ideanya ialah memasukkan berat baru setiap hari, dan mengira perbezaannya dari hari sebelumnya. Apabila setiap hari mempunyai entri, rumus mudah:

Perbezaannya dikira dengan formula seperti ini, dimasukkan dalam D6, dan disalin jadualnya:

=IF(C6"",C6-C5,"")

Walau bagaimanapun, apabila satu atau beberapa hari terlewat, semuanya menjadi serba salah, dan hasil yang dikira tidak masuk akal:

Tidak, anda tidak mendapat 157 paun dalam satu hari

Masalahnya ialah formula menggunakan sel kosong dalam pengiraan, yang dinilai menjadi sifar. Apa yang kita perlukan adalah cara untuk mencari dan menggunakan berat terakhir yang dicatatkan pada lajur C.

Cabaran

Formula apa yang akan mengira perbezaan dari entri terakhir, walaupun hari sudah dilangkau?

Hasil yang diinginkan - perbezaan menggunakan entri sebelumnya yang lalu

Andaian

  1. Satu formula dimasukkan dalam D6 dan disalin ke bawah (iaitu formula yang sama di semua sel)
  2. Rumus mesti mengendalikan satu atau banyak entri kosong sebelumnya
  3. Membuang entri kosong (baris) tidak dibenarkan
  4. Tiang penolong tidak dibenarkan

Nota: satu jalan yang jelas adalah menggunakan formula IF Nested. Saya tidak akan mengizinkan ini, kerana tidak akan berjaya mengatasi sebilangan entri kosong berturut-turut.

Ada jalan penyelesaian? Tinggalkan komen dengan formula cadangan anda di bawah.

Saya meretas formula sendiri, dan saya akan berkongsi penyelesaian saya setelah saya memberi pembaca pintar beberapa ketika untuk mengemukakan formula mereka sendiri.

Kredit lebih

Mencari lebih banyak cabaran? Inilah hasil yang sama, dengan format nombor tersuai digunakan. Apakah format nombor? Petunjuk: Saya menukar ini dari Mike Alexander di blog Bacon Bitsnya.

Jawapan (klik untuk mengembangkan)

Terdapat penyelesaian yang sangat baik di bawah ini, termasuk penyelesaian yang sangat ringkas dan elegan oleh Panagiotis Stathopoulos. Untuk rekod, saya menggunakan LOOKUP dan rangkaian yang berkembang:

=IF(C6"",C6-LOOKUP(2,1/($C$5:C5""),$C$5:C5),"")

Mekanik MENCARI masalah seperti ini dijelaskan dalam contoh ini.

Artikel menarik...